Results 1 to 5 of 5

Find cell, insert formula and bold cell via VBA

This is a discussion on Find cell, insert formula and bold cell via VBA within the Excel Questions forums, part of the Question Forums category; Hello, I am working on a macro for Excel that creates a summary of a huge data file for our ...

  1. #1
    Board Regular
    Join Date
    Nov 2007
    Location
    Chicago, IL
    Posts
    1,161

    Default Find cell, insert formula and bold cell via VBA

    Hello,

    I am working on a macro for Excel that creates a summary of a huge data file for our four regions. The summary consists of three columns. Column A is the regions and amount of days past due. Column B is the sales amount for that period. Column C is the percent of the total for that region. I have pasted some sample data below.

    REGION TOTAL SALES % OF SALES
    CENTRAL - 1 - 7 DAYS STOCKED $558,169.01 69.000%
    CENTRAL - 8 - 14 DAYS STOCKED $110,823.18 13.700%
    CENTRAL - 15 - 21 DAYS STOCKED $69,160.40 8.550%
    CENTRAL - 22 - 30 DAYS STOCKED $28,519.24 3.530%
    CENTRAL - OVER 30 DAYS - STOCKED $24,194.30 2.990%
    CENTRAL - OVER 60 DAYS - STOCKED $8,177.43 1.010%
    CENTRAL - OVER 90 DAYS - STOCKED $9,913.33 1.230%
    TOTAL $808,956.89

    Next would be a blank line and then start over with the northwest region...

    What I would like is a way to "find" the cell next to the right of the $808,956.89 (which is in column B) amount and insert a formula that would be the sum of the fields from the region above it (obviously each should equal 100%). Finally, I would be looking to make the cell with the $808,956.89 and cell with the sum formula bolded.

    Any help would be great!

    Thank you,
    Roger

  2. #2
    Board Regular iggydarsa's Avatar
    Join Date
    Jun 2005
    Location
    One of the Blue States
    Posts
    1,535

    Default Re: Find cell, insert formula and bold cell via VBA

    Assuming that after every SECTION there will be a cell in column A named TOTAL, this code should do the trick

    Code:
    Sub Macro1()
    Dim LR As Long: LR = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 1 To LR
        If Trim(Range("A" & i).Value) = "TOTAL" Then
            Range("C" & i).Formula = "=SUM(C" & i - 1 & ":C" & Range("C" & i - 1).End(xlUp).Row & ")"
        End If
    Next i
    End Sub

  3. #3
    Board Regular
    Join Date
    Nov 2007
    Location
    Chicago, IL
    Posts
    1,161

    Default Re: Find cell, insert formula and bold cell via VBA

    Wow. Excellent!! Works terrific. If only next time you would respond timely.

    I do have one other small item (I think). How can I do something similar and find "TOTAL" in column A and then bold the two cells to the right?

    Thank you,
    Roger

  4. #4
    Board Regular iggydarsa's Avatar
    Join Date
    Jun 2005
    Location
    One of the Blue States
    Posts
    1,535

    Default Re: Find cell, insert formula and bold cell via VBA

    Code:
    Sub Macro1()
    Dim LR As Long: LR = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 1 To LR
        If Trim(Range("A" & i).Value) = "TOTAL" Then
            Range("C" & i).Formula = "=SUM(C" & i - 1 & ":C" & Range("C" & i - 1).End(xlUp).Row & ")"
            Range("B" & i & ":C" & i).Font.Bold = True
        End If
    Next i
    End Sub
    I hope it's timely enough :]

  5. #5
    Board Regular
    Join Date
    Nov 2007
    Location
    Chicago, IL
    Posts
    1,161

    Default Re: Find cell, insert formula and bold cell via VBA

    Absolutely perfect! I couldn't have paid for better or faster help. Thank you very, very much. This was the last piece to the macro, so I finished prior to the weekend. Great feeling!

    Thank you again. Very, very much appreciated.
    Roger

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com