Converting formula to VBA

Question

I am very new to VBA, I have a formula with me, Which I want in Macro.

I used Macro recording to have the vba, but the Problem is it is generating for each row, making the vba code complex. also, it Displays the formula in formula bar. I have two Sheets , sheet 1 (BW) and sheet2(CW). I want the code in sheet 1. it Looks for ID in sheet 2 and copy the values from Ad to Au of sheet1.

I have tried, to some extent to implement my formula to code.

=IF(IFERROR(VLOOKUP($B2;CW!$B$2:$AU591;30;FALSE);"0")=0;" ";IFERROR(VLOOKUP($B2;CW!$B$2:$AU591;30;FALSE);""))

Sub lookupo()

Dim totalrows As Long
Dim totalrowssh2 As Long
 totalrows = Sheets("BW").Cells(Rows.Count, "A").End(xlUp).Row
    totalrowsSht2 = Sheets("CW").Cells(Rows.Count, "A").End(xlUp).Row
  sheets("BW").Range("AD2:AD" & Totalrows).formula = Application.WorksheetFunction.If(Iferror(Apllication.Vlookup(sheets("BW").Range("B2:B" &totalrowssht2), Sheets("CW").Range("$A:$AU"),29,False),"0"))=0,"")
End Sub

I am struck how i should implement the second formula in line. Could someone help me to overcome with a VBA code.

Answer 1

No need to use Application.WorksheetFunction. You can directly assign the formula string to a cell.

Is this what you are trying?

Sub lookupo()
    Dim BWlRow As Long, CWlRow As Long
    Dim Sformula As String
    Dim wsBW As Worksheet, wsCW As Worksheet

    Set wsBW = Sheets("BW"): Set wsCW = Sheets("CW")

    BWlRow = wsBW.Cells(wsBW.Rows.Count, "A").End(xlUp).Row
    CWlRow = wsCW.Cells(wsCW.Rows.Count, "A").End(xlUp).Row

    Sformula = "=IF(IFERROR(VLOOKUP($B2;CW!$B$2:$AU" & _
               CWlRow & _
               ";30;FALSE);""0"")=0;"" "";IFERROR(VLOOKUP($B2;CW!$B$2:$AU" & _
               CWlRow & _
               ";30;FALSE);""""))"

    wsBW.Range("AD2:AD" & BWlRow).Formula = Sformula
End Sub

Use this if ; is not your separator.

    Sformula = "=IF(IFERROR(VLOOKUP($B2,CW!$B$2:$AU" & _
               CWlRow & _
               ",30,FALSE),""0"")=0,"" "",IFERROR(VLOOKUP($B2,CW!$B$2:$AU" & _
               CWlRow & _
               ",30,FALSE),""""))"

How can i extend the same till column AU. ? – Mikz 4 mins ago

You will have to loop through the columns and amend the formula before applying it.

Sub lookupo()
    Dim BWlRow As Long, CWlRow As Long, i As Long
    Dim Sformula As String
    Dim wsBW As Worksheet, wsCW As Worksheet

    Set wsBW = Sheets("BW"): Set wsCW = Sheets("CW")

    BWlRow = wsBW.Cells(wsBW.Rows.Count, "A").End(xlUp).Row
    CWlRow = wsCW.Cells(wsCW.Rows.Count, "A").End(xlUp).Row

    For i = 30 To 47 '~~> Col AD to AU
        Sformula = "=IF(IFERROR(VLOOKUP($B2,CW!$B$2:$AU" & _
                   CWlRow & _
                   "," & _
                   i & _
                   ",FALSE),""0"")=0,"" "",IFERROR(VLOOKUP($B2,CW!$B$2:$AU" & _
                   CWlRow & _
                   "," & _
                   i & _
                   ",FALSE),""""))"

        With wsBW
            .Range(.Cells(2, i), .Cells(BWlRow, i)).Formula = Sformula
        End With
    Next i
End Sub

Answer 2

Application.WorksheetFunction is a good idea, if you think that one day your Excel would be used outside an English speaking country. Furthermore, you should not be worrying about the formula separators this way, Excel sets them automatically.

Having said that, try to use Option Explicit at the top of your file (this highlights variable definition errors immediately) and then correct your code and then fix a bit of it like this:

• totalrowssh2 - make sure that totalrowssht2 is the same everywhere.
• Apllication.Vlookup - take a look here and fix the grammer mistake.

Then try the code below and fix it a bit:

Sub lookupo()

    Dim totalrows       As Long
    Dim totalrowssh2    As Long

    totalrows = Worksheets("BW").Cells(Rows.Count, "A").End(xlUp).Row
    totalrowssh2 = Worksheets("CW").Cells(Rows.Count, "A").End(xlUp).Row
    Worksheets(1).Range("AD2:AD" & totalrows).Formula = Application.WorksheetFunction.If(WorksheetFunction.IfError(Application.VLookup(Sheets(1).Range("B2:B" & totalrowssh2), Sheets("CW").Range("$A:$AU"), 29, False), "0") = 0, "")

End Sub