Multiple if/ then VBA

[piercealcatraz]

Hello all,

I am using Excel 2003 and am frustrated trying this myself as I have very basic knowledge of Vba.

I have values in I2 and I3, and I want the code to run as a worksheet_change IF these two cells equal two other cells in W19 and X19, respectively, then I4 = Y19, I5 = Z19, I6 = AA19, etc. But if I2 and I3 = W20 and X20, then I4 = Y20, I5 = Z20, I6 = AA20, etc.

So something like...IF I2 = W19 and I3 = X19 Then
I4 = Y19, I5 = Z19, I6 = AA19 Else If
I2 = W20 and I3 = X20 Then
I4 = Y20, I5 = Z20, I6 = AA20

Thank you in advance.

 

[VoG]

Maybe like this

If Range("I2").Value = Range("W19").Value And Range("I3").Value = Range("X19").Value Then
    Range("I4").Value = Range("Y19").Value: Range("I5").Value = Range("Z19").Value: Range("I6").Value = Range("AA19").Value
ElseIf Range("I2").Value = Range("W20").Value And Range("I3").Value = Range("X20").Value Then
    Range ("I4"), Value = Range("Y20").Value: Range("I5").Value = Range("Z20").Value: Range("I6").Value = Range("AA20").Value
End If

 

[poolhall]

With Sheet1
     If .Range("I2").Value = .Range("W19").Value And .Range("I3").Value = .Range("X19") Then
           .Range("I4").Value = .Range("Y19").Value
           .Range("I5").Value = .Range("Z19").Value
           .Range("I6").Value = .Range("AA19").Value
     ElseIf .Range("I2").Value = .Range("W20").Value And .Range("I3").Value = .Range("X20") Then
           .Range("I4").Value = .Range("Y20").Value
           .Range("I5").Value = .Range("Z20").Value
           .Range("I6").Value = .Range("AA20").Value
     End If
End With

P.S. Change Sheet1 with a codename of your sheet.

 

[piercealcatraz]

Thanks guys! This is what I have built so far, and should function, but it's not doing anything. I had to put the Application.EnableEvents because when i ran the code it locked up my pc. Now nothing is happening.

Private Sub Worksheet_Change(ByVal Target As Range)
 With Sheet2
     If .Range("I2").Value = .Range("W19").Value And .Range("I3").Value = .Range("X19") Then
     Application.ScreenUpdating = False
     Application.EnableEvents = False
           .Range("I4").Value = .Range("Y19").Value
           .Range("I5").Value = .Range("Z19").Value
           .Range("I6").Value = .Range("AA19").Value
           .Range("I8").Value = .Range("AB19").Value
           .Range("K10").Value = .Range("AC19").Value
           .Range("I12").Value = .Range("AD19").Value
           .Range("K14").Value = .Range("AE19").Value
           .Range("I16").Value = .Range("AF19").Value
           .Range("K18").Value = .Range("AG19").Value
           .Range("I23").Value = .Range("AH19").Value
           .Range("K25").Value = .Range("AI19").Value
           .Range("I27").Value = .Range("AJ19").Value
           .Range("K29").Value = .Range("AK19").Value
           .Range("I31").Value = .Range("AL19").Value
           .Range("K35").Value = .Range("AM19").Value
           .Range("K36").Value = .Range("AN19").Value
           .Range("K37").Value = .Range("AO19").Value
           .Range("H38").Value = .Range("AP19").Value
           .Range("K38").Value = .Range("AQ19").Value
           .Range("K39").Value = .Range("AR19").Value
           .Range("I40").Value = .Range("AS19").Value
           .Range("I41").Value = .Range("AT19").Value
           .Range("I42").Value = .Range("AU19").Value
     ElseIf .Range("I2").Value = .Range("W20").Value And .Range("I3").Value = .Range("X20") Then
           .Range("I4").Value = .Range("Y20").Value
           .Range("I5").Value = .Range("Z20").Value
           .Range("I6").Value = .Range("AA20").Value
           .Range("I8").Value = .Range("AB20").Value
           .Range("K10").Value = .Range("AC20").Value
           .Range("I12").Value = .Range("AD20").Value
           .Range("K14").Value = .Range("AE20").Value
           .Range("I16").Value = .Range("AF20").Value
           .Range("K18").Value = .Range("AG20").Value
           .Range("I23").Value = .Range("AH20").Value
           .Range("K25").Value = .Range("AI20").Value
           .Range("I27").Value = .Range("AJ20").Value
           .Range("K29").Value = .Range("AK20").Value
           .Range("I31").Value = .Range("AL20").Value
           .Range("K35").Value = .Range("AM20").Value
           .Range("K36").Value = .Range("AN20").Value
           .Range("K37").Value = .Range("AO20").Value
           .Range("H38").Value = .Range("AP20").Value
           .Range("K38").Value = .Range("AQ20").Value
           .Range("K39").Value = .Range("AR20").Value
           .Range("I40").Value = .Range("AS20").Value
           .Range("I41").Value = .Range("AT20").Value
           .Range("I42").Value = .Range("AU20").Value
     Application.EnableEvents = True
     Application.ScreenUpdating = True
     End If
 End With

End Sub

 

[VoG]

Make sure that events are enabled. Press CTRL + G to open the Immediate Window, type

Application.EnableEvents = True


then press Enter.

 

[prajul89]

Make sure that events are enabled. Press CTRL + G to open the Immediate Window, type

Application.EnableEvents = True


then press Enter.

What does Immediate Window do, I have seen it many time but never use it.

 

[VoG]

What does Immediate Window do, I have seen it many time but never use it.

It is very useful. See http://vbatips.com/2007/12/05/using-the-immediate-window/

 

[prajul89]

Thanks for the link. Will study it.

 

[prajul89]

It is very useful. See http://vbatips.com/2007/12/05/using-the-immediate-window/

Its really very useful. This utility will make writing codes easier. Thanks a lot.

 

[piercealcatraz]

I did as you said with the immediate window and entering "Application.EnableEvents = True", but after the second half of the code runs it flickers my screen as if it is running non-stop to where I have to break the code. Once I break it and hit "debug" it highlights the line containing
.Range("K39").Value = .Range("AR20").Value
Then I have to re-enter "Application.EnableEvents = True" back into the immediate window for it to execute again and it starts all over.