Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Posts
    5

    Question Unanswered: Formatting cells within a macro

    I'm a novice.

    I would like to format certain columns a without identifying them as "A1-A21" and so on but rather "1-7" within a given range.

    I am copying ranges from different files into the main worksheet in a different workbook. After that happens I want the data to be formatted a certain way. I am looking for a way to standardize the formatting so I don't have to change the column references for each range.
    So, basically what I am saying is I need a piece of code that will say columns 1-2 within the already specified range should be formatted as %, 3-7 as coma.

    Thanks a lot.

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Re: Formatting cells within a macro

    Here's a bit of code that might help

    Code:
        With Selection
            .Range("A1:B1").EntireColumn.NumberFormat = "0.00%"
            .Range("C1:G1").EntireColumn.Formula = "#,##0"
        End With
    This should apply the number formats which you require.
    this works by looking at the selection and picking a range within that
    Selection i.e. the .Range("A1:B1") looks at the two upperleft cells of the
    selection

    Hope this Helps
    David

  3. #3
    Join Date
    Apr 2004
    Posts
    5
    Sorry, I don't think I was clear.
    That piece of code will work but only for my first cut and paste. That's because my the second file that i'll be copying will be pasted on the same worksheet to the right of the first cut and paste. So if I use your code, I'd have to change the column references for my second section and all the rest.
    This becomes a big problem because I'll have over 50 files that will be combined into one with multiple sections fitting into each worksheet.
    I hope this makes sense.

    Thanks a lot for you help.

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Your Reply is Not Quite right

    after you cut and paste data in the region you paste into is now selected
    as long as you are on the same worksheet.

    give my code a try on any section of length 7 and you will see that it selects a range according to the range already selected

    for example if your selection was columns D through J

    the code i gave you will format columns D and E to Percentage
    and C through G as thousand seperator

    so when you cut and paste the new data into your workbook.
    run the code i gave you and it will change the selected columns

    David

  5. #5
    Join Date
    Apr 2004
    Posts
    5
    I'll be working within different worksheets.
    I tried your sample but it didn't work.
    Here's my whole code- maybe that will help.

    Sub UBD_SubProcess(fname As String, shtMain As Worksheet, rngMain As Range)

    Dim wkbk As Workbook
    Dim sht As Worksheet
    Dim rng As Range

    Const path = "C:\Documents and Settings\"

    Set wkbk = Application.Workbooks.Open(path & fname)
    Set sht = wkbk.ActiveSheet
    Set rng = sht.Range("E12:P72")

    sht.Activate
    rng.Select
    Selection.Copy

    shtMain.Activate
    rngMain.Select
    ActiveSheet.Paste

    With Selection
    .Range("A1:B1").EntireColumn.NumberFormat = "0.00%"
    .Range("C1:G1").EntireColumn.Formula = "#,##0"
    End With


    Application.CutCopyMode = False
    Set rngMain = rngMain.Offset(0, 15)
    wkbk.Close SaveChanges:=False

    End Sub

    Sub UpdateBudgetsData()

    Dim fname As String
    Dim wkbkMain As Workbook
    Dim shtMain As Worksheet
    Dim rngMain As Range

    Set wkbkMain = ActiveWorkbook
    Set shtMain = wkbkMain.Worksheets("Rev")
    Set rngMain = shtMain.Range("E12")


    '1ST FILE
    fname = "1REV.xls"
    Call UBD_SubProcess(fname, shtMain, rngMain)

    '2ND FILE
    fname = "2REV.xls"
    Call UBD_SubProcess(fname, shtMain, rngMain)

    '3RD FILE
    ' fname = "3REV.xls"
    ' Call UBD_SubProcess(fname, shtMain, rngMain)


    '************************************************
    'Now move onto another sheet within the main file
    Set shtMain = wkbkMain.Worksheets("REV2")
    Set rngMain = shtMain.Range("E12")

    '4TH FILE
    fname = "4REV.xls"
    Call UBD_SubProcess(fname, shtMain, rngMain)

    End Sub

    Again thank for taking your time.

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    you were almost there i had to change just a couple of things.
    Your Macro should now run smoothly

    Code:
    Sub UBD_SubProcess(fname As String, shtMain As Worksheet, rngMain As Range)
    
    Dim wkbk As Workbook
    Dim sht As Worksheet
    Dim rng As Range
    
    Const path = "C:\Documents and Settings\"
    
    Set wkbk = Application.Workbooks.Open(path & fname)
    Set sht = wkbk.ActiveSheet
    Set rng = sht.Range("E12:P72")
    
    rng.Copy
    rngMain.PasteSpecial
    
    With rngMain
        .Range("A1:B1").EntireColumn.NumberFormat = "0.00%"
        .Range("C1:G1").EntireColumn.NumberFormat = "#,##0"
    End With
    
    
    Application.CutCopyMode = False
    Set rngMain = rngMain.Offset(0, 15)
    wkbk.Close SaveChanges:=False
    
    End Sub
    
    Sub UpdateBudgetsData()
    
    Dim fname As String
    Dim wkbkMain As Workbook
    Dim shtMain As Worksheet
    Dim rngMain As Range
    
    Set wkbkMain = ActiveWorkbook
    Set shtMain = wkbkMain.Worksheets("Rev")
    Set rngMain = shtMain.Range("E12")
    
    
    '1ST FILE
    fname = "1REV.xls"
    Call UBD_SubProcess(fname, shtMain, rngMain)
    
    '2ND FILE
    fname = "2REV.xls"
    Call UBD_SubProcess(fname, shtMain, rngMain)
    
    '3RD FILE
     fname = "3REV.xls"
     Call UBD_SubProcess(fname, shtMain, rngMain)
    
    
    '************************************************
    'Now move onto another sheet within the main file
    Set shtMain = wkbkMain.Worksheets("REV2")
    Set rngMain = shtMain.Range("E12")
    
    '4TH FILE
    fname = "4REV.xls"
    Call UBD_SubProcess(fname, shtMain, rngMain)
    
    End Sub
    the only changes i made were to stop sheets activating when required
    and to change my selection to your range, and a formula to a numberformat. (I put formula in for my own testing and forgot to change it back again)

    I hope this helps

    David

  7. #7
    Join Date
    Apr 2004
    Posts
    5
    I am sorry to keep bugging you but.... the formatting piece doesn't work.
    I made the recommended changes and it cuts and pastes fine but the formatting "doesn't take" meaning I don't get an error but it doesn't format the desired columns either.
    Thanks again.

  8. #8
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    where does the problem lie

    ive just tested the code above and it seems to format the data fine
    it copies the data accross to the worksheet 'REV'
    and then it formats the data such that the first 2 columns are percentages with 2 d.p.s and the next 5 of the copied range are thousand seperated.

    the rest of your 12 columns are not formatted.
    could this be where the problem lies.

    David

  9. #9
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    can i ask you to do me a favour im still trying to figure out whats going wrong

    can you update the bit of code that i provided to say

    Code:
    With rngMain
        .Range("A1:B1").EntireColumn.NumberFormat = "0.00%"
        .Range("C1:G1").EntireColumn.Interior.ColorIndex = 3
    End With
    and see which columns are colored red

    this should see if the formatting is taking hold or if the type of formatting isnt right

    David

  10. #10
    Join Date
    Apr 2004
    Posts
    5
    Sorry it took me so long to reply.
    I looked at it again this morning and realized that I was making a mistake: I never renamed selection as rngMain.
    So Now everything works as you said it should.
    Thanks you so much.

Posting Permissions

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