Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?
    <font color=blue>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</font color=blue>

    TIA
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Doesn't Find Blanks (Excel XP-SP3)

    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

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Doesn't Find Blanks (Excel XP-SP3)

    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?

    <font color=blue>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 </font color=blue>
    TIA
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Doesn't Find Blanks (Excel XP-SP3)

    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.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Doesn't Find Blanks (Excel XP-SP3)

    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:

    <pre>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
    </pre>

    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Doesn't Find Blanks (Excel XP-SP3)

    Thanks Hans! That does it!!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Doesn't Find Blanks (Excel XP-SP3)

    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!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Doesn't Find Blanks (Excel XP-SP3)

    Hans,
    I am trying to modify the MissingAccounts Function to check for 3 things:
    <UL><LI>If cells C2 and C3 (in any ws) are blank but cell P95 <>0 - or
    <LI>if cell C2 is blank and C3 >0 and P95 <>0 - or
    <LI> if cell C3 is blank and C2 >0 and P95 <>0[/list]The code starts out with:

    <font color=blue>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
    <font color=black>'The next line begins the original macro</font color=black>
    Application.ScreenUpdating = False
    For Each ws In Worksheets</font color=blue> etc.

    I changed the Function to read:
    <font color=blue>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</font color=blue>

    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?
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Doesn't Find Blanks (Excel XP-SP3)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •