Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2003
    Location
    London,UK
    Posts
    54

    Unanswered: formating cells to currency

    how to i format a rang of cells (k7:r34) to currency with 2 deciamal places within vba.

    any help as usual much appreciated.

    Jas

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Whenever you have a question about VBA and it does not involve decision/loops, then use the macro recorder to go through the steps of what you want to do. Sometimes it might be inefficient (more lines of code than necessary) but it will give you what you want.

    The recorder will give you this:
    Code:
    Sub Macro1()
    
    Range("K7:R34").Select
    Selection.Style = "Currency"
    
    End Sub
    This can be shortened to:

    Code:
    Range("K7:R34").Style = "Currency"
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Aug 2003
    Location
    London,UK
    Posts
    54
    Thanks for the advice, means i wont be posting as many queries up here i dont think. Thanks a lot.

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Posting here is fine. But you will begin to learn the VBA procedures by using the recorder. I know many world-class VBA-ers who still use the recorder if they happen not to know (or remember) how to do some particular action.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Aug 2003
    Location
    London,UK
    Posts
    54
    thanks. only 1 problem i am having with the code you wrote. I have a access db and what i am doing is runing some calculations from the database and exporting the data to and excel spreadsheet. Some of the data needs to be currency hence the original question.

    when i run the macro from access, the spreadsheet gets created, the data gets exported and formatting (currency occurs.) close excel down and run the macro again it fails on the currency part of the code. if i do it again it does complete.

    Its like every second time the code is run it fails.

    any help

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    run the macro again it fails on the currency part of the code. if i do it again it does complete.
    Not sure what you mean. Do you mean that on the same set of data? Or newly imported data?

    -----------------

    BTW, in my original post about cleaning up what the recorder did, note that if you have "Select on one line, and "Selection" beginning the next line you can take those out and make it one line - you don't need to select in order to do something with the range/cell/etc.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Aug 2003
    Location
    London,UK
    Posts
    54
    yep, on the same set of data.

    If i ran my code now from access the currency stuff will work.

    when i close down excel and go back to access and run again, it fails on the Range("k7:r34").Select. with the following error.

    Run time error 1004 : Method 'Range' of object'_Global' failed.

    any help?

  8. #8
    Join Date
    Oct 2003
    Posts
    1,091
    Just a couple more questions.

    After the data is first imported into Excel (before this Excel macro is run the first time), what is the format of the cells in question?

    If you run the Access again, does it refresh or replace the data?

    Would it work for you to link the table in Excel to the table/query in Access?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  9. #9
    Join Date
    Aug 2003
    Location
    London,UK
    Posts
    54
    i must tell you that all the code is in access, no code is in excel.

    The app that am making is for 10-15 users that have very little IT knowledge so i am trying to automate all the extract from the database for them.

    all the code is in access not excel.

  10. #10
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, I was under the impression you were writing code in Excel.

    I was going to suggest that you write the code in Excel and use MS Query to bring in the data from Access. Not sure that would help.

    Regarding the code in Access, can you post the portion that you use to export to Excel?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  11. #11
    Join Date
    Aug 2003
    Location
    London,UK
    Posts
    54
    Originally posted by shades
    Okay, I was under the impression you were writing code in Excel.

    I was going to suggest that you write the code in Excel and use MS Query to bring in the data from Access. Not sure that would help.

    Regarding the code in Access, can you post the portion that you use to export to Excel?
    ok here it is

    Code:
    Dim currentRow As Integer, currentColumn As Integer
        currentRow = 1
        currentColumn = 1
        Set myXL = New Excel.Application
        myXL.Visible = True
        myXL.Workbooks.Add        'Creates a new workbook
        myXL.ActiveSheet.Name = "Decent Homes Data Extract"
        
        With myXL.ActiveSheet
            .Cells(currentRow, currentColumn).value = tm1
            currentRow = 31
            .Cells(currentRow, currentColumn).value = tm4
            currentRow = 32
            .Cells(currentRow, currentColumn).value = tm3
            currentRow = 33
            .Cells(currentRow, currentColumn).value = tm5
            currentRow = 34
            .Cells(currentRow, currentColumn).Font.Bold = True
            .Cells(currentRow, currentColumn).value = tThermal
    
            
            'Application.References ("k7:r36")
            'Application.Goto Reference = "k7:r36"
            'Selection.NumberFormat = "#,##0"
            currentRow = 28
            currentColumn = 11
            .Cells(currentRow, currentColumn).NumberFormat = "#,##0"
            
    
    
            
            currentColumn = 2
            currentRow = 5
            'Range("B5:I5").Merge
            'Range("K5:R5").Merge
            .Cells(currentRow, currentColumn).Font.Bold = True
            .Cells(currentRow, currentColumn).value = "Decent Homes Property Failures"
            currentColumn = 11
    
            .Cells(currentRow, currentColumn).Font.Bold = True
            .Cells(currentRow, currentColumn).value = "Decent Homes Costings"
            'Range("k7:r36").AutoFormat (xlRangeAutoFormatAccounting1)
            .Cells.Columns.AutoFit

  12. #12
    Join Date
    Oct 2003
    Posts
    1,091
    'Selection.NumberFormat = "#,##0"
    currentRow = 28
    currentColumn = 11
    .Cells(currentRow, currentColumn).NumberFormat = "#,##0"
    It seems like you are formatting currency but without the two decimals.

    Try:


    'Selection.NumberFormat = "#,##0.##"
    currentRow = 28
    currentColumn = 11
    .Cells(currentRow, currentColumn).NumberFormat = "#,##0.##"
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •