Multiple IF statements to VBA code

heinmiller · May 14th 2016

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(B1=B2,"Y","N")
=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"," ")
=IF(OR(M2="MISMATCH",AND(B2=B1,N1="SELECT BIN")),"SELECT BIN"," ")

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

skywriter · May 14th 2016

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.

heinmiller · May 16th 2016

Re: Multiple IF statements to VBA code

Got that part - thank you! Now how do I get it to copy down for all existing cells?

skywriter · May 16th 2016

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.

Example:
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:

Code

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

Multiple IF statements to VBA code

Participate now!

Share