Calling a variable assigned to an object

Question

This is a small portion of my code:

If cell1.Offset(0, 8) = "Alpha" Then
   Set cell_target = cell2
ElseIf cell1.Offset(0, 8) = "Bravo" Then
   Set cell_target = cell3
Else
   MsgBox "ERROR"
   End
End if

where cell1 is just a cell inside a for...next loop within myRange on Sheet1 and cell2 and cell3 are just single cells (range "A2") located on Sheet2 and Sheet3.

At the end of my code, I want to shift either cell2 or cell3 (depending on what cell_target is in the code above) down by one row, which I do in the following line of code:

If cell_target = cell2 Then
   Set cell2 = cell2.Offset(1)
ElseIf cell_target = cell3 Then
   Set cell3 = cell3.Offset(1)
Else
   MsgBox "ERROR"
   End
End if

The problem is that all of the cells are treated as cell2 even if they are not. I have worked around this problem by writing:

If cell1.Offset(0, 8) = "Alpha" Then
   Set cell2 = cell2.Offset(1)
ElseIf cell1.Offset(0, 8) = "Bravo" Then
   Set cell3 = cell3.Offset(1)
Else
   MsgBox "ERROR"
   End
End if

I am just curious why the first method did not work. My personal guess is that by typing cell_target = cell2, the code was not checking the reference name (cell2 or cell3) (which is what I need) but whether cell2 is an object or not; and since it was, all the cells ended up being treated as cell2. I would greatly appreciate if you could either confirm my understanding or refute it and clarify this issue for me. Should my description be too vague, I could provide my code in full either here or via an Excel file.

Your test is only checking if the values are the same, not whether they are the same cells. You could test the .Address property or use: If cell_target Is cell2 Then — Rory
@Rory is right. Just exercise caution when comparing the .Address properties (esp. if the cells are on different sheets or workbooks) because Sheet1.Range("A1").Address = Sheet2.Range("A1").Address would return True. — Ambie
Good point made by @Ambie - if you use the address make sure to use .Address(External:=True) — Rory
@Rory and @Ambie, thank you very much for your insightful comments! Is operator is exactly what I needed, @Rory! Should you wish to re-post your first comment as an answer, I will tick it as such. — Tango_Mike

Rory Rory · Accepted Answer · 2016-04-13T12:59:00

Per the earlier comments, your test is only checking if the values are the same, not whether they are the same cells.

You could test the .Address(External:=True) property or use:

If cell_target Is cell2 Then

Small note about the Is operator with ranges: it will work here because you assigned one range variable to the other directly. However if you had assigned say Range("A1") to two variables, the Is operator would return False when you compared them, because the Range property returns a new reference each time.

Calling a variable assigned to an object

2 Answers