searching merged cells

Originally Posted by chris_mayer

Hi

I am searching groups of cells to see if there are empty cells within a particular range.

However, although my code works the search counts all the cells that make up a merged cell so says that I have many empty cells when I do not!

I.e. I have 10 cells for instance, but each of those 10 cells is made up of 15 merged cells. Therefore, If my 10 cells are empty, my code reports 150 cells as been empty rather than 10. If I fill in my 10 merged cells, it reports 140 cells as been empty..

Part of my code is below. Can anyone help??

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

Set MaxNum = Range("AF5").Value

Length = Len(MaxNum)

Holder = Range("B27:AG29")

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."

'count each blank cell in a range
For Each x In Holder
If IsEmpty(x.Value) Then EmpCell = EmpCell + 1
Next x

If Answer > 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,

display EmpCell / 10

or do you have a mixed range search?
---

Originally Posted by chris_mayer

Hi

Sorry, only used that as an example for ease of explanation. My merged cells are made up different numbers of merged cells so that wouldn't work :-(

Aaaha - ok

You can cycle through the range of cells and test if each is a part of a Merge, as per the help on 'Merged' -
Set ma = Range("a3").MergeArea
If Range("a3").MergeCells Then
ma.Cells(1, 1).Value = "42"
End If

but you would need a serious reason to do that.

---

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.

---