cell.offset when starting cell is merged
 

Merged cells can be a pain. Try using the Cell.Offset(20, 0).Activate and
then add a line of code:

ActiveCell.Offset(0, 1)

That should get you there.

"JNW" wrote:

I am using an if statement to perform two tests before continuing the code.
In the code below, the ranges are drop down cells. Rng1 is actually L12 and
M12 merged. (I tried to get around this with center across selection, but
visually it doesn't work when you have a validation drop down).

For Each cell In Range("Rng1,Rng2,Rng3,Rng4")
If left(cell.Value,1) = 2 And cell.Offset(20, 1).Value < 1 Then
GoTo LeaveMe1
End If
Next cell

I need to check two things:
1. Is Rng1 on option 2?
2. Is the sum of monthly percentages (located in M32) not equal to 100%

If both of these are true then I've got a msgbox that tells the user and
then the sub exits.

The problem is with the offsetting from the merged cell. When I use
cell.offset(20,1) from the merged L12:M12 I get N32. When I use
cell.offset(20,0) I get L32.

Now for the questions.
-Is there a way to show a validated dropdown across two cells without merging?
-If not, how do I select M32 with offset? (or another way if it is better).
I've also tried adding lines to offset down and select, then offset right and
select then use the selection. However, I'd like to avoid the use of
selection.


--
JNW