Looping Through Rows To Evaluate Mulitiple Columns?

LionRunner

New Member
Joined
Nov 13, 2014
Messages
19
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>
 
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,833
Office Version
  1. 2019
Platform
  1. Windows
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.

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

Dave
 

LionRunner

New Member
Joined
Nov 13, 2014
Messages
19
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

Board Regular
Joined
Aug 6, 2013
Messages
211
Code:
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
 

Forum statistics

Threads
1,143,654
Messages
5,720,090
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top