Thanks:  0
Likes:  0

# Thread: Find cell, insert formula and bold cell via VBA

1. ## 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. ## 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. ## 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. ## 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. ## 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

## User Tag List

#### Posting Permissions

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