Looping only first cell in a merged cell

**johncassell** · 11-16-2011, 11:47 AM

Hi, I have a named range which looks at several cells (most of which are merged). I am looping to find out if the cell as a value in it or not but its not working as I expect.

For example, cell A1:A5 is merged. I am asking my loop to look at cell A1 only, but it insists on checking A2,A3,A4 & A5 as well.

Can anyone advise a way of avoiding this please?

Sub SetMandatoryBoxes()
Range("F6,B8,F8").Select
ActiveWorkbook.Names.Add Name:="Mandatory", RefersTo:=Selection
End Sub

F6 is merged through to R6.
B8:D8
F8:H8

How can I get it to only look at the first cell in the merge?

Thanks in advance
John

**6StringJazzer** · 11-16-2011, 12:17 PM

You are not showing the code for the loop. You are assuming the problem is that fact that the cells are merged, but I am guessing that the problem is in how you coded it. I did what you describe and it only iterates on the specified cells.

Are you iterating over a selection? If you do this, it will iterate through all cells in the merged range. When you select any cell in a merged range, you select the whole merged range.

Range("F6").Select
For Each c In Selection
   ' Every cell in the merged range will be processed
Next c

For Each c In Range("F6")
   ' Only cell F6 will be processed
Next c

In most cases where you see Select or Selection appearing in VBA code it needs a second look.

**johncassell** · 11-17-2011, 03:55 AM

Sorry, should have posted the loop (and thanks for the reply)

Basically checks to see if any of the cells have a string length of less than 2 and then colours the cell

Sub CheckAllBoxes()
For Each CELL In Range("Mandatory")
CELLADD = CELL.Address
If Len(CELL.Value) < 2 Then
CELL.Interior.ColorIndex = 3
CELL.Font.ColorIndex = 5
Count = Count + 1
End If
Next
If Count > 0 Then
MsgBox "Please complete all mandatory fields. The missing fields will be highlighted red."
End If
End Sub

thanks
John

**nilem** · 11-17-2011, 04:29 AM

maybe this:

Sub SetMandatoryBoxes()
Dim r As Range
Set r = Union([b8].MergeArea.Cells(1), [f6].MergeArea.Cells(1), [f8].MergeArea.Cells(1))
'Range("F6,B8,F8").Select
ActiveWorkbook.Names.Add Name:="Mandatory", RefersTo:=r
End Sub

**johncassell** · 11-17-2011, 04:36 AM

Hi, I've now took note of what 6StringJazzer was saying and have now scrapped the named range and instead of looping the selection, I am now just looping the actual range and its working perfect

For Each cell In Range("F6:F6,B8:B8")...

didn't need to try your code Nilem but thank you for your response.

John

**6StringJazzer** · 11-17-2011, 09:04 PM

Originally Posted by johncassell

Basically checks to see if any of the cells have a string length of less than 2 and then colours the cell

I took a second look at this--did you know that you can do this with conditional formatting without VBA code?

**johncassell** · 11-18-2011, 04:10 AM

Hi, yes I am using that as well. All boxes start off as a pale yellow. When user enters some data it turns bright green (conditional formatting). When they hit the 'Send' macro button this is when it says 'Some boxes are empty' and then fills them all in red to make it obvious.

John