Code Doesn’t Find Blanks (Excel XP-SP3)

Topic

New Reply

I have attached a copy of a macro that copies/pastes information from many sheets to one. Everything works fine but I would like to add a snip of code. Before it does anything I would like to check for a blank cell C2 in any worksheet. If it finds a blank, a message box should pop up and then the entire sub should quit. The user can rerun the macro after the missing info has been taken care of. If there are no blank C2s the macro would continue with the 1st “Do While”. This is what I tested outside of the main macro, with all the C2 cells blank ,but I didn’t get an error message. The macro just ran and quit. Can you tell me what I am saying incorrectly?
Sub MissingAccounts()
‘Application.ScreenUpdating = False
For Each ws In Worksheets
If Range(“c2″) = ” ” Then
MsgBox “You have Missing Activity Codes”
End If
Next
End Sub

TIA

Reply | Quote

Replies

You’re testing for a space ” “, not for an empty value “”, and you don’t specify that Range(“C2”) should refer to the worksheet ws.

Perhaps you should make MissingAccounts into a function returning True or False:

Function MissingAccounts() As Boolean
Dim ws As Worksheet
For Each ws In Worksheets
‘ Test cell C2 on the worksheet ws for being “”
If ws.Range(“C2”) = “” Then
MsgBox “You have Missing Activity Codes”
‘ Set return value
MissingAccounts = True
‘ No need to go on checking
Exit For
End If
Next ws
End Function

Call like this:

If MissingAccounts = True Then Exit Sub

Reply | Quote

Hans, I’ve never used a Function in a macro before. I put the Function code in the macro and then called it at the very top of the routine but I got an error message “Ambiguous name detected: MissingAccounts. What am I missing?

Sub TransferData()

Dim PickRowVal As Double
Dim PutRowVal As Double
Dim TestForValue As Variant
Dim ws As Worksheet
PutRowVal = 3
If MissingAccounts = True Then Exit Sub
rest of code
TIA

Reply | Quote

You probably left the original Sub MissingAccounts in one of your modules. You should either delete it, or rename it so that the ambiguity is removed.

Reply | Quote

Thanks Hans! That does it!!

Reply | Quote

Hans,
I am trying to modify the MissingAccounts Function to check for 3 things:

• If cells C2 and C3 (in any ws) are blank but cell P95 0 – or
• if cell C2 is blank and C3 >0 and P95 0 – or
• if cell C3 is blank and C2 >0 and P95 0
  [/list]The code starts out with:

  Sub TransferData()

  Dim PickRowVal As Double
  Dim PutRowVal As Double
  Dim TestForValue As Variant
  Dim ws As Worksheet
  PutRowVal = 3

  If MissingData = True Then Exit Sub
  ‘The next line begins the original macro
  Application.ScreenUpdating = False
  For Each ws In Worksheets etc.

  I changed the Function to read:
  Function MissingData() As Boolean
  Dim ws As Worksheet
  For Each ws In Worksheets
  If ws.Range(“C2”) = “” And ws.Range(“C3”) = “” And ws.Range(“P95”) 0 Then
  MsgBox “You have Missing Data. Check for a CC and Activity in Each Worksheet.”
  MissingData = True
  ElseIf ws.Range(“C2”) = “” And ws.Range(“C3”) 0 And ws.Range(“P95”) 0 Then
  MsgBox “You have Missing Activity Codes”
  MissingData = True
  ElseIf ws.Range(“C3”) = “” And ws.Range(“C2”) 0 And ws.Range(“P95”) 0 Then
  MsgBox “You have Missing Cost Centers Numbers”
  MissingData = True
  Exit For
  End If
  Next ws
  End Function

  When I run the macro I do not get an error message no matter what combination of missing data I use. It has to be how I am wording the IF statement…. yes?

Reply | Quote

The code works OK whenI try it. What do C2, C3 and P95 contain? If they contain spaces, that does not count as “”. As Legare remarked, you can get around that by using Trim:

If Trim(ws.Range(“C2”)) = “” etc.

Reply | Quote

If you want to check for either a blank or an empty cell, and also exit from the Sub if it is found, I would do it this way:

Sub MissingAccounts()
'Application.ScreenUpdating = False
    For Each ws In Worksheets
        If Trim(Range("C2")) = "" Then
            MsgBox "You have Missing Activity Codes"
            Exit Sub
        End If
    Next
' The rest of your code goes here.
End Sub

Reply | Quote

Legare,
That works too! I was trying to figure out how to have that routine run upfront and then, if no errors, continue on.

Thanks much!

Reply | Quote