How to enter formulas in to cells using a macro

[ste33uka]

Hi would anyone have a vba that would send theses formulas to cells for multiple sheets?
my workbook has 50 pages named,1,2,3,4,5,6 and so on
Thanks

J4 would be =IF(C4<>"",INDEX(Comments!$A$2:$A$22222,MATCH(C4,Comments!$N$2:$N$22222,0)+3))
J8 would be =IF(C8<>"",INDEX(Comments!$A$2:$A$22222,MATCH(C4,Comments!$N$2:$N$22222,0)+3))
J12 would be =IF(C12<>"",INDEX(Comments!$A$2:$A$22222,MATCH(C4,Comments!$N$2:$N$22222,0)+3))

 

[offthelip]

try this code:

Sub test()
Dim shtn As String

For i = 1 To 50
shtn = i
With Worksheets(shtn)
.Range("J4").Formula = "=IF(C4<>" & Chr(34) & Chr(34) & ",INDEX(Comments!$A$2:$A$22222,MATCH(C4,Comments!$N$2:$N$22222,0)+3))"
.Range("J8").Formula = "=IF(C8<>" & Chr(34) & Chr(34) & ",INDEX(Comments!$A$2:$A$22222,MATCH(C8,Comments!$N$2:$N$22222,0)+3))"
.Range("J12").Formula = "=IF(C12<>" & Chr(34) & Chr(34) & ",INDEX(Comments!$A$2:$A$22222,MATCH(C12,Comments!$N$2:$N$22222,0)+3))"
End With
Next i
End Sub

 

[ste33uka]

try this code:

Sub test()
Dim shtn As String

For i = 1 To 50
shtn = i
With Worksheets(shtn)
.Range("J4").Formula = "=IF(C4<>" & Chr(34) & Chr(34) & ",INDEX(Comments!$A$2:$A$22222,MATCH(C4,Comments!$N$2:$N$22222,0)+3))"
.Range("J8").Formula = "=IF(C8<>" & Chr(34) & Chr(34) & ",INDEX(Comments!$A$2:$A$22222,MATCH(C8,Comments!$N$2:$N$22222,0)+3))"
.Range("J12").Formula = "=IF(C12<>" & Chr(34) & Chr(34) & ",INDEX(Comments!$A$2:$A$22222,MATCH(C12,Comments!$N$2:$N$22222,0)+3))"
End With
Next i
End Sub

Thanks but result is
=@IF(C4<>"",INDEX(Comments!$A$2:$A$22222,MATCH(C4,Comments!$N$2:$N$22222,0)+3))
excel adds @ at beginning

 

[offthelip]

It doesn't with my version of Excel!! It may be a reference to tables, and the equation may work perfectly ok. see this link:
Excel 365 - Functions preceded by "@" in formulas

 

[Fluff]

Are you using xl 365? if so use Formula2 rather than Formula

 

[ste33uka]

Thanks offthelip and fluff, code works when i add formula2.

 

[Fluff]

As you are using 365 you might want to update your profile.