Need a formula to loop the VB Macro.

August 1, 2011 at 09:56:58
Specs: Windows XP
I have the following piece of code:

Windows("TEMP Release BoW.xlsx").Activate
Application.CutCopyMode = False
Windows("TEMP Release Control Matrix.xlsx").Activate

Windows("TEMP Release Control Matrix.xlsx").Activate
Range("M4").Formula = "= S4 - 42"

I want to ensure that cells M5, M6 onwards need to populate the values S5-42, S6-42 etc.. until the last cell in which M contains a value.
Pleaseee help!!!!

See More: Need a formula to loop the VB Macro.

Report •

August 1, 2011 at 11:43:51
Let's start with the fact that your rarely need to actually Select a range or object for VBA to perform an action on it.

You'll have to test this, but it looks to me as if your first 7 lines can be replaced with this single line (assuming Sheet1 is the right sheet in each book...modify as needed)

Workbooks("TEMP Release BoW.xlsx").Sheets(1).Range("AG3:AG207").Copy _
   Destination:=Workbooks("TEMP Release Control Matrix.xlsx").Sheets(1).Range("S4")

As for your question, I'd just like to check something...

You say you want to fill Column M from M4 to the last cell in Column M that contains a value. Should I take that to mean that you want to overwrite what is currently in Column M with the "=Sx - 42" formula?

This code should do that for you. On the other hand, if you are looking to fill Column M up until the last cell in some other column just change the Column letter in the lastRw line of this code:

(By using the R1C1 notation, RC[6] tells VBA to retain the Row number of where formula is placed but increment the Column number by 6 (M + 6 = S)

Sub FillFormula()
'Determine last cell with data in Column B
 lastRw = Range("M" & Rows.Count).End(xlUp).Row
'Fill M4:M(last row) with formula
    Range("M4:M" & lastRw).FormulaR1C1 = "=RC[6]-42"
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question