Find cell, insert formula and bold cell via VBA

rconverse

Well-known Member
Joined
Nov 29, 2007
Messages
1,187
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
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
 
Upvote 0
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 :]
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,710
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top