cell.offset when starting cell is merged
 

Sorry, I did not read that very carefully. The problem is that you are using
the merged cell as a reference point and the system knows it is two cells and
therefore tries to compensate for that in any relative reference. It will
recognize column "L" as the beginning of its location and column "M" as the
end of its horizontal location, Which means that vertically from that merged
cell, everything is Column L, but one column to the right is column "N" and
vertically from that cell reference, column "M" does not exist. So your code
will fail when trying to capture "M32". Your option is to make your target
"L32" or "N32".

"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