VBA Code Needed That Considers Variable Change

[JohnnyAngel]

Trying to create a VBA code that will enter a formula such as: SUM(Q2:Qt) in a range of cells for instance A2:A10, and if there is information in B11, B12, B13 etc., it will consider that variable change and enter the SUM formula in cells A11, A12, A13.

I read that Dim LR As Long is the solution but not sure how to apply.

 

[MrKowz]

Can you please elaborate a bit on this? I'm unsure exactly what you're asking. Are you wanting to put the same SUM formula in each cell in column A that has a corresponding B value?

 

[JohnnyAngel]

Yes that's correct. Currently, when I record a macro it does all these functions but only up to a specific cell that was indicated. Hence, I have to manually change the code to ensure it copies the formula down to the next cell. I assume there is a code that looks for data in corresponding columns as an indicator to copy the formula down.

 

[MrKowz]

Something like this?

Public Sub FillColA()
Dim LR  As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("A2:A" & LR).Formula = "=SUM(Q2:QT2)"
End Sub

 

[JohnnyAngel]

Perfect. Thank you.

 

[JohnnyAngel]

One last question, How do I modify the script/code so that I can copy and paste the values that the formulas calculated?

I want the VBA code to first post the formula in all the cells considering variable change.

Then, copy the range the formulas using again variable change command and simply paste the values after.

 

[JohnnyAngel]

Looking for the code that will allow me to copy and paste the information after the procedure described in this thread.

Underneath is what I have so far. The items in bold are the ones I need the right code and sentence structure for.

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("C2:C" & LR).Formula = "=VLOOKUP(RC1,'Nat Acct List'!R2C1:R1150C5,2,FALSE)"
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("L2:L" & LR).Formula = "=VLOOKUP(RC1,'Nat Acct List'!R2C1:R1150C5,3,FALSE)"
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("M2:M" & LR).Formula = "=VLOOKUP(RC1,'Nat Acct List'!R2C1:R1150C5,4,FALSE)"
LR = Range("A" & Rows.Count).End(xlUp).Row
Range ("C2:C" & LR).
Selection.Copy = "C2:C"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
​

 

[JohnnyAngel]

Anyone available to answer this question by chance?

 

[MrKowz]

Try:

Public Sub FillColA()
Dim LR  As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
With Range("A2:A" & LR)
    .Formula = "=SUM(Q2:QT2)"
    .Value = .Value
End With
End Sub

 

[JohnnyAngel]

Worked excellent. Thank you!