VBA Macro Divide a Range of Cells by a Constant Cell (different constant cell location per spreadsheet)

[allstarrunner]

Hi, and thank you in advance for your help. This is probably pretty simple for someone who knows what they are doing!

I have 100 numbers, lets say A1:A100. I want to divide A1, A2, etc., by the sum of A1:A100 all the way down in the B column. For Example =A1/A$101$ (A101 being the Sum total of A1:A100); however, since this is in a macro, each spreadsheet will have a different cell location for the sum total.

Thanks again for your help!

 

[VoG]

Maybe like this

Sub test()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LR + 1).Formula = "=SUM(A1:A" & LR & ")"
Range("B1:B" & LR).Formula = "=A1/A$" & LR + 1
End Sub

 

[allstarrunner]

Thank you very much, it worked perfectly!
One last question if you don't mind: I need to then select the range of the cells in the B column and want to copy and paste the values. I don't know if I need to actually copy and paste to do that, but I want to convert the formula's to values. Thanks!

 

[VoG]

Try

Sub test()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LR + 1).Formula = "=SUM(A1:A" & LR & ")"
With Range("B1:B" & LR)
    .Formula = "=A1/A$" & LR + 1
    .Value = .Value
End With
End Sub

 

[allstarrunner]

Worked perfectly, thank you very much!