Auto Formula

[ExcelRoy]

Hi,

I am looking to set up an auto formula macro which i can get to work fine

But the following part of the formula is what i am having trouble with

Cells(i, "H").Formula = "=IF(P1_F_T_0" & x & "="""","""",SUM(H" & a & ":H" & b & ")))"

What i need is

"a" to show 13, "b" to show 23 on the first formula
"a" to show 175, "b" to show 185 on the second formula
"a" to show 337, "b" to show 347 on the third formula
"a" to show 499, "b" to show 509 on the fourth formula

etc, etc

Here's the complete code

Sub AutoFormulaEntry()
x = 1
For i = 26 To 16064 Step 162
Cells(i, "H").Formula = "=IF(P1_F_T_0" & x & "="""","""",SUM(H" & a & ":H" & b & ")))"
x = x + 1
Next i
End Sub

Thanks

 

[Jonmo1]

try

Sub AutoFormulaEntry()
Dim x As Long, i As Long
x = 1
For i = 26 To 16064 Step 162
    Cells(i, "H").FormulaR1C1 = "=IF(P1_F_T_0" & x & "="""","""",SUM(R[-13]C:R[-3]C))"
    x = x + 1
Next i
End Sub

 

[cgcamal]

Hi ExcelRoy,

Another option:

Sub AutoFormulaEntry()
x = 1
For i = 26 To 16064 Step 162
Cells(i, "H").Formula = "=IF(P1_F_T_0" & x & "="""","""",SUM(H" & (i - 13) & ":H" & (i - 3) & ")))"
x = x + 1
Next i
End Sub

Hope this helps.

Regards.

 

[ExcelRoy]

Now thats what im talking about!

Both works better, i think i will go with the cgcamal one as i understand it more!

Thanks cgcamal

Thanks Jonmo

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[cgcamal]

You're welcome. Nice to know it works for you.

Regards.

 

[ExcelRoy]

Hi,

Actually cgcamal's version doesn't work, so its Jonmo's version for me!

Thanks

 

[Jonmo1]

Hi,
Actually cgcamal's version doesn't work, so its Jonmo's version for me!
Thanks

That probably doesn't work for the same reason your original posted didn't work...
One too many ) at the end

Cells(i, "H").Formula = "=IF(P1_F_T_0" & x & "="""","""",SUM(H" & (i - 13) & ":H" & (i - 3) & ")))"
should be
Cells(i, "H").Formula = "=IF(P1_F_T_0" & x & "="""","""",SUM(H" & (i - 13) & ":H" & (i - 3) & "))"

 

[ExcelRoy]

Yup, that'll be the reason.

Sorry guys, but thanks for the solutions

 

[cgcamal]

Thanks for the correction jonmo1!

Regards