Looping Through Rows To Evaluate Mulitiple Columns?

[LionRunner]

Data is set up as such, with first row being titles:

--A --------------B------------------ C----------------- D
Blank0 ------Luxury Status -------Region--------- Region Flag
---------------Non-Luxury ----------Central
---------------Non-Luxury ---------- East
---------------Non-Luxury ---------- Midwest
---------------Luxury ---------- --- Central

Etc., etc. etc.


I wrote this initial set of code to accomplish my task for just one row and it works perfectly
</SPAN>
Sub Region()</SPAN>
Dim Luxury As String, Region As String, Flag As String, curCell As Range</SPAN>
Luxury = Range("B2").Value
Region = Range("C2").Value</SPAN>
If Luxury = "Non-Luxury" And (Region = "Central" Or Region = "East") Then Flag = "NLR"</SPAN>
Range("D2").Value = Flag</SPAN>
End Sub

It reads column B and C to see if the Regions are the named type and returns NLR to column D if they are, and returned nothing when I removed the word Central; as it should.

After I had proof of concept I needed to loop this through about 2000 rows. So I wrote the next set of code. It does perform the loop but returns NLR for every single row no matter what is in the corresponding B and C column. </SPAN>
Sub Region3()</SPAN>
Dim Luxury As String, Region As String, Flag As String, i As Integer</SPAN>
Range("D2").Activate
For i = 1 To 25
Luxury = Cells(i, 2).Value
Region = Cells(i, 3).Value</SPAN>
If Luxury = "Non-Luxury" And (Region = "Central" Or Region = "East") Then Flag = "NLR"</SPAN>
Cells(i, 4).Value = Flag
Next
End Sub


It seems to be recognizing that i is the row because it loops through row 2001 and it recognize that the 4 in the Flag statement is column D because it returns NLR to column D, it just seems to not be making the evaluation that the If statement requires.

Thank you for your help!</SPAN>

 

[dmt32]

It does perform the loop but returns NLR for every single row no matter what is in the corresponding B and C column.

Your variable Flag is retaining the last value when your If condition is True - you need to set it to another value when condition is False e.g.

If Luxury = "Non-Luxury" And (Region = "Central" Or Region = "East") Then
        Flag = "NLR"
            Flag = "NLR"
        Else
            Flag = ""
        End If

Dave

 

[LionRunner]

Great!

It worked, and I was able to take it from there and add ElseIf statements to apply the rest of my conditions.

Should have figured I needed an Else statement, but since the If worked without it on the one row version I didn't think about it being stored in the variable when looping. Its obvious now that it wouldn't store NLR in the 1 row programing as it never met the condition for NLR to be placed in Flag when I altered the columns being evaluated.

Thanks a lot Dave.

 

[chrism216]

Sub Region3()Dim Luxury As String, Region As String, Flag As String, i As Integer
Range("D2").Activate
For i = 1 To ActiveSheet.UsedRange.Rows.Count
    Luxury = Cells(i, 2).Value
    Region = Cells(i, 3).Value
    If Luxury = "Non-Luxury" And (Region = "Central" Or Region = "East") Then
        Flag = "NLR"
        Cells(i, 4).Value = Flag
    End If
Next
End Sub