Solved: How to Write Formulas in VBA?

[khaledocom]

Good morning all,

In column A starting from A6 there are some values, I need in coumn B

Starting from B6 the following formula:
"=IF(A6<>"",$A$2&A6,"")"

Starting from C6 the formula:
"=IF(A6<>"",IF(ISNA(VLOOKUP(LEFT(A6,8),Data!A:H,2,0)),"Not Available",VLOOKUP(LEFT(A6,8),Data!A:H,2,0)),"")"

Starting from D6 the formula:
"=IF(A6<>"",IF(ISNA(VLOOKUP(LEFT(A6,8),Data!A:H,3,0)),"Not Available",VLOOKUP(LEFT(A6,8),Data!A:H,3,0)),"")"

and fill down to last used cell in column A

Please help, Thanks.

[GarysStudent]

Use a string variable for the double-quotes:

[VBA]Sub dural()
Dim N As Long, dq As String, ddq As String
N = Cells(Rows.Count, "A").End(xlUp).Row
dq = Chr(34)
ddq = dq & dq
Cells(6, 2).Formula = "=IF(A6<>" & ddq & ",$A$2 & A6," & ddq & ")"
Cells(6, 2).Copy Range("B7:B" & N)
End Sub
[/VBA]

[Kenneth Hobs]

Normally, I like to build a formula string as Gary showed.

A rule-of-thumb, add an extra quote for each quote in a string. e.g.
[VBA]Sub ken()
Range("B6").Formula = "=IF(A6<>"""",$A$2&A6,"""")"
Range("C6").Formula = "=IF(A6<>"""",IF(ISNA(VLOOKUP(LEFT(A6,8),Data!A:H,2,0)),""Not Available"",VLOOKUP(LEFT(A6,8),Data!A:H,2,0)),"""")"
Range("D6").Formula = "=IF(A6<>"""",IF(ISNA(VLOOKUP(LEFT(A6,8),Data!A:H,3,0)),""Not Available"",VLOOKUP(LEFT(A6,8),Data!A:H,3,0)),"""")"
Range("B66").AutoFill Destination:=Range("B6" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
End Sub[/VBA]

[mikerickson]

Recording a macro is a good way to get a formula into the R1C1 format and it can be transported to other ranges as needed.

[khaledocom]

Thanks a lot Kenneth, it was really useful...worked good.
Thanks a lot GarysStudent and mikerickson.