searching merged cells

Originally Posted by chris_mayer

Thanks Bryan

I will have a look at what you have suggested.

Unfortunately, it is important for me to validate these cells to see if they are empty. The sheet in question is a legal document and it is very important that all cells have something entered. I don't want to have to write code for each individual cell in the sheet becuase that would mean, in my opinion a lot of code and no easy way of telling the user (excel novices) which cell they need to fill in.

The sheet is split into sections so my idea was to seach each section and bring up an error telling the user that there was x number of cells in that section that are empty.

I thought it would be simple, whoops!

Hi,

It may be, you already have a loop to count, so you could add the 'Merged' test to that and save the merged range and not count future cells in that range.

Not perfect, but depending on how you merged might be OK

---

Originally Posted by chris_mayer

Hi Bryan

Thanks, I have had a go and to be honest I am still slightly confused to how it works but it does seem to work and I have added some of my own bits too (hark at me)!!

However, something very odd is happening. When I was first testing it, it looked like it wasn't working becuase it wasn't picking up blank cells even when they were blank.

However, when I selected a blank cell and pressed 'delete' on the key pad and rand the check again, it recognised it as a blank cell and brought up the error.

And even then, if I run the check again, immediately after without doing anything, it doesn't recognise the previously empty cell as been empty.

I am stumped.


Here is the code I am using:

Sub check_cells()

Dim Err As String
Dim Length As String
Dim MaxNum As String
Dim EmpCell As Integer
Dim Holder As Range

'set the variables

MaxNum = Range("AF5").Value

Length = Len(MaxNum)

Set Holder = Range("Appliance_Details")

EmpCell = 0

If Range("AF5").Value = "" Then Err = "You must enter the Maximo Number."
If Length < 5 Then Err = "You have not entered a valid Maximo Number"

If Range("W11").Value = "" Then Err = "You must enter the customer name and location."

'Check Merged cells
For Each x In Holder
If x.Value = ">>Error<<" Then x.Value = "" ' Reset any errors
Set ma = x.MergeArea ' select merged area
If x.MergeCells Then ' if cell is merged then
If IsEmpty(ma.Cells(1, 1).Value) And ma.Cells(1, 1).Value <> ">>Error<<" Then ' If the first cell of the merged area is null and not equal to error then
EmpCell = EmpCell + 1 ' Add one to count
ma.Cells(1, 1).Value = ">>Error<<" ' Set cell error message
End If
End If
Next x

If EmpCell > 0 Then Err = "There are " & EmpCell & " cells not completed"

If Err <> "" Then
MsgBox Err, vbCritical
Else
MsgBox "This sheet is ready to save and send!"
End If

End Sub

HI,

at 1:08am it all looks good, (or fuzzy), are you on Manual calculation ? (would that affect anything?)


Hope you spot it, otherwise I will try tomorrow.

---