want to check for empty cell with vba

Please Login or Register  to view this content.

I want to loop through cells of worksheet tab name "fruit" column F starting at row 6 to the last row of data
checking if there are any empty cells. The code is not working though.

Please offer suggestion

Originally Posted by humdingaling

Please Login or Register  to view this content.

not sure you require arrays to do what your doing
making it more complicated than it needs to be

Thank you that worked. How would I do this for 3 tabs that I would like to check please?

many thanks that worked superbly

Please Login or Register  to view this content.

could you explain this line please?

if you want to skip a tab then add an extra IF statement at the start of the loop to skip it
please explain what the line of code would be:

if ws.name = "fred" then ...

Originally Posted by humdingaling

Please Login or Register  to view this content.

i put this in because in my testing i found that if there is nothing in column F
this is the address that will come thru

so i want to skip a whole section of the for each ws in worksheets Loop
it goes straight to None:

Please Login or Register  to view this content.

if this is not what you want to happen...then i suggest you re-route it to something else

Please Login or Register  to view this content.

if tab is either bob fred or jim then it will skip

many thanks that worked great

Thanks

I tried modifying the code to account for the range starting at different columns on different worksheets but it yields incorrect result saying empty cell at F6 in tab "jam" (which is true) although i tried setting it to check from range H7?

Please Login or Register  to view this content.

sorry I fixed it I saw problem was the <> I changed to =.

Originally Posted by humdingaling

attached the working file i was using to test
added some extra comments
hope it helps

Many Thanks

Hi!

I am new to VBA. If the cell are empty, I want a message pop up and ask user to fill before proceed and the excel should not be able to save if not filled. I searched the forum and found a post related to it and have code. But when I run it, I keep getting error message invalid outside procedure. If I want to run the same marco from C50-55, can I set code for once or have to do it 5 times ( meaning go into each cell’s code and paste the same code)
Thank you.

If IsEmpty(Range("c50")) Then
Select Case MsgBox("c50 is empty. Do you want to continue?", vbYesNo Or _
vbQuestion Or vbDefaultButton1, "Select option")

Case vbYes
'This must be filled
Case vbNo
Exit Sub
End Select
End If

Originally Posted by nsue

Hi!

I am new to VBA. If the cell are empty, I want a message pop up and ask user to fill before proceed and the excel should not be able to save if not filled. I searched the forum and found a post related to it and have code. But when I run it, I keep getting error message invalid outside procedure. If I want to run the same marco from C50-55, can I set code for once or have to do it 5 times ( meaning go into each cell’s code and paste the same code)
Thank you.

If IsEmpty(Range("c50")) Then
Select Case MsgBox("c50 is empty. Do you want to continue?", vbYesNo Or _
vbQuestion Or vbDefaultButton1, "Select option")

Case vbYes
'This must be filled
Case vbNo
Exit Sub
End Select
End If

Sorry I am not expert so I do not know solution.
It is better you start a new post with your problem stated there . Then the forum expert can help you.