Cells Method using Ranges

[montanaheather]

Hello, all,

I am working with the below code. I did not write the code, but I am commenting it so that others can understand what each line is doing (we are hiring a new person that has limited coding experience).

I would like to understand how rowRng is used in the code. Specifically, why does the Cells property remain unchanged (e.g. Cells(1,2) never changes), but rowRng changes? The code moves from one row to the next, so I don't understand why the Cells property isn't something like, Cells(x,2), where x changes with each loop. uniquecount is a function used to count the number of unique values in the column.

Here is the code:

Dim mapRng As Range
Dim tblQuestion As Range
Dim rowRng As Range

For Each rowRng In mapRng.Rows
If rowRng.Cells(1, 2) = "X" Then
rowRng.Cells(1, 3) = "X"
rowRng.Cells(1, 4) = "X"

Else
If rowRng.Cells(1, 2) > 0 And rowRng.Cells(1, 2) < 10000000 Then
If tblQuestion.Columns(1).Find(rowRng.Cells(1, 2)).Offset(0, 3) <> "" Then
rowRng.Cells(1, 3) = "QL"

Else
rowRng.Cells(1, 3) = "OPEN"
End If

Else
rowRng.Cells(1, 3) = "DEMO"
End If

If rowRng.Cells(1, 3) = "DEMO" Then
rowRng.Cells(1, 4) = rowRng.Cells(1, 2)

Else
If uniquecount(rowRng.Cells(1, 2), mapRng) = 1 Then
rowRng.Cells(1, 4) = "Main"

Else
rowRng.Cells(1, 4) = "TRANSPOSE"

End If
End If
End If
Next rowRng

[Norie]

Something like this refers to column y in row x of the range rowRng.

rowRng.Cells(x,y)

So if rowRng was referring to row 1 rowRng.Cells(1,2) would refer to B1, rowRng.Cells(1,3) to C1 and rowRng.Cells(1,4) to D1.

If it referred to row 2 rowRng.Cells(1,2) would refer to B2, rowRng.Cells(1,3) to C2 and rowRng.Cells(1,4) to D1

And so on.

[montanaheather]

Okay, that helps, thank you. I am still a little confused, though. Take the following lines of code as an example:

Dim rowRng as Range
Dim mapRng as Range

For Each rowRng In mapRng.Rows  
    If rowRng.Cells(1, 2) = "X" Then
    rowRng.Cells(1, 3) = "X"
    rowRng.Cells(1, 4) = "X"
Next rowRng 

The macro loops through each row in column B to check if there is an "X" is each cell in that column. If there is an "X", then the macro places an "X" in columns C and D.

I am confused as to how the macro knows to move to the next row when the loop stays on Cells(1,2) in each loop. How does the changing rowRng affect the row that is being checked for an "X"? That is, when the macro gets to the Next rowRng line, how is the next rowRng value used to move to the next row when Cells(1,2) never changes?

[Norie]

Cells(1,2) is relative to rowRng so the row it refers to changes as rowRng changes.

[montanaheather]

That helps, thank you! I guess I am confused on how the rowRng variable and the mapRng variable can be used together if they are both ranges. How can you use a range (rowRng) with the cells property to define which row you are looking with another range varaiable (mapRng)?

Thank you for your help! I appreciate it!

[Norie]

Imagine each range as a mini worksheet with it's own rows and columns.

So when you have rngRows.Cells(1,2) the Cells(1,2) refers to a cell in the mini worksheet rngRows.

Similarly with rowRng and mapRng.Rows, rowRng is referring to rows in the mini worksheet mapRng.

PS For rngRow.Cells(1,2) you could actually use rngRow.Range("B1"), but that would just be confusing.

[montanaheather]

That is amazing! Thank you, thank you! That simple explanation made all the difference between me pretending to understand to actually understanding the code - and now I will be able to use it (not just muddle through it) in future macros!

Thank you, again!