Vba for each cell in rng insert dynamic formula

[Alpacino]

Hi all,

this might be simple but can’t get my head around it.

i would like to put a formula into each cell within a range (B2:AZ517)

Then paste the value then move onto the next cell.

The only thing is that the formula contains references e.g. row 1 and column A must stay constant.

for example the cell B2 would be Sum(B$1,$A2) moving onto next row
B3 cell would be Sum(B$1,$A3)

anyone help please.. ?

 

[igold]

This...

Sub test()


    Range("B2").Formula = "=sum(B$1,$A2)"
    Range("B2").AutoFill Range("B2:B517")
    
End Sub

 

[Alpacino]

Hi igold

Many thanks for this, however I need to paste the values from the formula into each of cell before moving onto the next one, is this something you could help with ?.

 

[igold]

If I understand your requirement, does this do what you want...

Sub test()


    Dim arr
    Range("B2").Formula = "=sum(B$1,$A2)"
    Range("B2").AutoFill Range("B2:B517")
    arr = Range("B2:AZ517")
    Range("B2").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
    
End Sub

 

[DanteAmor]

Try this:

Sub test1()
  Range("B2:AZ517").Formula = "=Sum(B$1,$A2)"
End Sub

If you need the values

Sub test1()
  With Range("B2:AZ517")
    .Formula = "=Sum(B$1,$A2)"
    .Value = .Value
  End With
End Sub

 

[Alpacino]

Thank you igold ��

 

[Alpacino]

ThA k you Dante

 

[igold]

You're welcome.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.