Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofit Merged cell Code is changing the format of my merged cells | Excel Discussion (Misc queries) | |||
cell.offset when starting cell is merged | Excel Programming | |||
how do i link merged cells to a merged cell in another worksheet. | Excel Worksheet Functions | |||
Transpose? Offset? Variable starting cell | Excel Discussion (Misc queries) | |||
how to offset when it is a merged cell. | Excel Programming |