PDA

View Full Version : Solved: How to Write Formulas in VBA?



khaledocom
06-08-2013, 04:48 AM
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
06-08-2013, 05:33 AM
Use a string variable for the double-quotes:

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

Kenneth Hobs
06-08-2013, 09:50 PM
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.
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("B6:D6").AutoFill Destination:=Range("B6:D" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
End Sub

mikerickson
06-09-2013, 01:54 AM
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
06-09-2013, 08:12 AM
Thanks a lot Kenneth, it was really useful...worked good.
Thanks a lot GarysStudent (http://www.vbaexpress.com/forum/member.php?u=46311) and mikerickson (http://www.vbaexpress.com/forum/member.php?u=10706).