VBA noob needs help.

[Halljrod]

I need excel to insert a formula into a row i.e. (A3:G3) then skip down to Row 5 and insert the same formula. I need Excel to do this all the way down to row 10080. I have tried combining a few VBA codes but nothing has worked. The code that shows the most promise is:

Sub xyz()
Dim i As Integer
For i = 3 To 10080 Step 2
Cells(i, 1).Formula = "=SUM(B1:C1)"
Next
End Sub

However, this formula only inserts my formula into column A. Please help!

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

Cells(i, 1).Resize(, 7).FormulaR1C1 = "=SUM(R[-2]C[1]:R[-2]C[2])"

 

[Halljrod]

That's interesting, I never thought about changing the actual formula into a different format. This also presents a huge problem because the formula that I posted with my question is not the formula that I need to replicate. The formula in question is:

=IF(A2="FL",MAX(INDEX('Draft HUD'!$O$12:$U$15,,MATCH(A1,'Draft HUD'!$O$11:$U$11,0))),INDEX('Draft HUD'!$O$12:$U$15,MATCH(Permutations!A2,'Draft HUD'!$N$12:$N$15,0),MATCH(Permutations!A1,'Draft HUD'!$O$11:$U$11,0)))

Should I change this to AR1C1 or change the way the VBA is structured? Thanks for all of your help thus far!

 

[Andrew Poulsom]

Turn on the macro recorder and enter the formula manually. You should then get some code that uses R1C1 style references.