for, if, and, end if

**intothewild** · 04-14-2014, 12:06 PM

im trying to achieve the following

if val a = val b and val a > 5000 then val q = val q
else
if val a = val b and val a is between 1000 and 5000 then val q = val q - 0.25
else
if val a = val b and val a is between 500 and 1000 then val q = val q - 0.5
else
if val a = val b and val a is between 250 and 500 then val q = val q - 0.75
else
if val a = val b and val a < 250 then val q = 0

the first and last statements seem to work but the middle three wont which seems due to the double Ands
how do i write the properly and is there a way to write the code more economically?

 Dim j As Integer
 Dim k As Integer
 Dim l As Integer
 Dim m As Integer
 Dim n As Integer
 
 For j = 2 To lastrowPW Step 1
  If Sheets("PW_TRI").Range("L" & j).Value = Sheets("PW_TRI").Range("M" & j).Value And Sheets("PW_TRI").Range("L" & j).Value > Sheets("Controls").Range("H12").Value Then
  Sheets("PW_TRI").Range("T" & j).Value = Sheets("PW_TRI").Range("Q" & j).Value
  End If
 Next j

 For k = 2 To lastrowPW Step 1
 If Sheets("PW_TRI").Range("L" & k).Value = Sheets("PW_TRI").Range("M" & k).Value
  AND Sheets("PW_TRI").Range("L" & k).Value >= Sheets("Controls").Range("G12").Value AND Sheets("PW_TRI").Range("L" & k).Value < Sheets("Controls").Range("G13").Value Then
  Sheets("PW_TRI").Range("T" & k).Value = Sheets("PW_TRI").Range("Q" & k).Value - Sheets("Controls").Range("G14").Value
  End If
 Next k
 
 For l = 2 To lastrowPW Step 1
  If Sheets("PW_TRI").Range("L" & l).Value = Sheets("PW_TRI").Range("M" & l).Value
  AND Sheets("PW_TRI").Range("L" & l).Value >= Sheets("Controls").Range("F12").Value AND Sheets("PW_TRI").Range("L" & l).Value < Sheets("Controls").Range("F13").Value Then
  Sheets("PW_TRI").Range("T" & l).Value = Sheets("PW_TRI").Range("Q" & l).Value - Sheets("Controls").Range("F14").Value
  End If
 Next l
  
 For m = 2 To lastrowPW Step 1
  If Sheets("PW_TRI").Range("L" & m).Value = Sheets("PW_TRI").Range("M" & m).Value
  AND Sheets("PW_TRI").Range("L" & m).Value >= Sheets("Controls").Range("E12").Value AND Sheets("PW_TRI").Range("L" & m).Value < Sheets("Controls").Range("E13").Value Then
  Sheets("PW_TRI").Range("T" & m).Value = Sheets("PW_TRI").Range("Q" & m).Value - Sheets("Controls").Range("E14").Value
  End If
 Next m
 
 For n = 2 To lastrowPW Step 1
  If Sheets("PW_TRI").Range("L" & n).Value = Sheets("PW_TRI").Range("M" & n).Value And Sheets("PW_TRI").Range("L" & n).Value < Sheets("Controls").Range("D13").Value Then
  Sheets("PW_TRI").Range("T" & n).Value = Sheets("PW_TRI").Range("Q" & n).Value
  End If
 Next n

**TMS** · 04-14-2014, 12:18 PM

I suggest you post a sample workbook with some typical data, the code and instructions on how to make it work/fail.

Regards, TMS

**Mad-Mizer** · 04-14-2014, 12:19 PM

You need to add underscores when using two lines for one line of code:

 Dim j As Integer
 Dim k As Integer
 Dim l As Integer
 Dim m As Integer
 Dim n As Integer
 
 For j = 2 To lastrowPW Step 1
  If Sheets("PW_TRI").Range("L" & j).Value = Sheets("PW_TRI").Range("M" & j).Value And Sheets("PW_TRI").Range("L" & j).Value > Sheets("Controls").Range("H12").Value Then
  Sheets("PW_TRI").Range("T" & j).Value = Sheets("PW_TRI").Range("Q" & j).Value
  End If
 Next j

 For k = 2 To lastrowPW Step 1
 If Sheets("PW_TRI").Range("L" & k).Value = Sheets("PW_TRI").Range("M" & k).Value _
  And Sheets("PW_TRI").Range("L" & k).Value >= Sheets("Controls").Range("G12").Value And Sheets("PW_TRI").Range("L" & k).Value < Sheets("Controls").Range("G13").Value Then
  Sheets("PW_TRI").Range("T" & k).Value = Sheets("PW_TRI").Range("Q" & k).Value - Sheets("Controls").Range("G14").Value
  End If
 Next k
 
 For l = 2 To lastrowPW Step 1
  If Sheets("PW_TRI").Range("L" & l).Value = Sheets("PW_TRI").Range("M" & l).Value _
  And Sheets("PW_TRI").Range("L" & l).Value >= Sheets("Controls").Range("F12").Value And Sheets("PW_TRI").Range("L" & l).Value < Sheets("Controls").Range("F13").Value Then
  Sheets("PW_TRI").Range("T" & l).Value = Sheets("PW_TRI").Range("Q" & l).Value - Sheets("Controls").Range("F14").Value
  End If
 Next l
  
 For m = 2 To lastrowPW Step 1
  If Sheets("PW_TRI").Range("L" & m).Value = Sheets("PW_TRI").Range("M" & m).Value _
  And Sheets("PW_TRI").Range("L" & m).Value >= Sheets("Controls").Range("E12").Value And Sheets("PW_TRI").Range("L" & m).Value < Sheets("Controls").Range("E13").Value Then
  Sheets("PW_TRI").Range("T" & m).Value = Sheets("PW_TRI").Range("Q" & m).Value - Sheets("Controls").Range("E14").Value
  End If
 Next m
 
 For n = 2 To lastrowPW Step 1
  If Sheets("PW_TRI").Range("L" & n).Value = Sheets("PW_TRI").Range("M" & n).Value And Sheets("PW_TRI").Range("L" & n).Value < Sheets("Controls").Range("D13").Value Then
  Sheets("PW_TRI").Range("T" & n).Value = Sheets("PW_TRI").Range("Q" & n).Value
  End If
 Next n

Now, I don't know if this actually solves your logical problems but...