Multiple IF statements to VBA code

  • I want to add 4 IF statements to an existing vlookup in my VBA code, but Im having a hard time figuring out how. I can write formulas, but I am very new to VBA
    The formulas are
    =IF(AND(K2="Y",J1<>J2),"NOT OK","OK")
    =IF(AND(K2="START BIN",OR(AND(B2=B3,L3="NOT OK"),(AND(B2=B4,L4="NOT OK")),(AND(B2=B5,L5="NOT OK")),(AND(B2=B6,L6="NOT OK")),(AND(B2=B7,L7="NOT OK")))),"MISMATCH"," ")

    Each formula will start on row 2 (same sheet) and I want to populate all the way down the column where data is present (each import has different # of rows)

    my vlookup code from one sheet to another below works great, but I cant figure out where to start to add the if statements. Any help would be appreciated.

    Sub formulas()
    With Sheets("Sheet1").Range("G2", Sheets("Sheet").Cells(Rows.Count, "G").End(xlUp))
    .Offset(, 3).Formula = "=VLOOKUP(G" & .Row & ",'sheet2'!$A:$D,3,FALSE)"
    .Offset(, 3).Value = .Offset(, 3).Value
    End With
    End Sub

  • Re: Multiple IF statements to VBA code

    A good way to figure out how to write a formula in VBA is to first write the formula into a cell.

    Then record a macro, choose the cell with the formula in it and hit enter.

    Stop recording and examine the code.

    You will then see how VBA would write that formula into that same cell using code.

  • Re: Multiple IF statements to VBA code

    If you write a formula into a cell using VBA and that formula could normally be copied to other cells once it's added to the first cell, then it can be written to all the cells at once.

    Formula in A1 is =B1+C1.

    With VBA we write a formula for cell A1 that if typed into cell A1 manually could be copied down column A.

    If written into all the cells at once with A1 being the first cell, then Excel writes it into all the other cells correctly, as if it were copied from A1 down.

    With VBA:

    Sub WriteFormula()
        With Range("A1:A10")
            .Formula = "=B1+C1"
        End With
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!