Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2006
    Posts
    60

    Unanswered: Excel range problem with VBA

    Hello All -

    I'm having a peculiar problem with an Excel app that I inherited. The problem, in a nutshell, is that when I leave a blank in a "memo" field, I should get an error message slapping the user on the wrist to fill in ALL flds. But, in my loop, the computer sees the first memo fld as the only fld in my loop, so that if I have, say, ONE fld with text in it, fine - it thinks all's well, pass to the next page. But, if that 1st fld is BLANK it gives me an error. Fine.
    BUT - if the 1st memo fld is filled in AND the OTHERS are blank, it SHOULD flag me with a warning message. Instead it goes on to the next page!!

    I've put in msgboxes and looked at the way the code is falling through. My present configuration gives me ALMOST what I want. And that's not good enough.

    I have a zipped version of the file - it is an add-in/template; not your usual spreadsheet. I can give anyone who wants to try the zip file and instructions for finding the problem area if they ask.

    For those of you who want a quick glance at the code here it is:

    Case gsSHEET_IVV_TAILORING 'Tailoring Justification page
    ActiveWindow.FreezePanes = False

    Dim rngfullCell As Range

    For Each rngfullCell In Range("rngJustRows")

    If rngfullCell.Value <> "" Then 'All filled in
    'then advance to next screen
    sSheetTab = sSheetTabName(wkbBook, gsSHEET_LABOR_HOURS)
    Set wksSheet = wkbBook.Worksheets(sSheetTab)

    'activate sheet and make visible
    wksSheet.Activate
    wksSheet.Visible = True
    Exit Sub
    End If

    MsgBox "PLEASE ENTER ALL JUSTIFICATIONS", vbOKOnly
    Exit Sub

    If rngfullCell.Value = "" Then

    MsgBox "PLEASE ENTER ALL JUSTIFICATIONS", vbOKOnly
    Exit Sub
    End If

    If rngfullCell.Value = 0 Then 'No justs
    'then advance to next screen
    sSheetTab = sSheetTabName(wkbBook, gsSHEET_LABOR_HOURS)
    Set wksSheet = wkbBook.Worksheets(sSheetTab)

    'activate sheet and make visible
    wksSheet.Activate
    wksSheet.Visible = True
    Exit Sub
    End If
    Next rngfullCell


    Much appreciation to anyone who wants to help. I've asked the guy who wrote the program if he could also offer any insights. And I'm working on it myself this weekend.

    Mike

  2. #2
    Join Date
    Sep 2006
    Posts
    60
    Oh, one thing forgot to mention. It seems the function which hides the rows after the ones that show up as being "picked" by the true/false values may be futzing things up. All I know is, my code above seems to see the first memo filled in and assumes that that's ALL the memos. So it allows it to pass.
    Sorry.

  3. #3
    Join Date
    Sep 2006
    Posts
    60
    I tried data validation this weekend, which seems to help, but my code above is still quirky.

  4. #4
    Join Date
    Sep 2007
    Posts
    7
    Hi , I don't know alot of VBA but I tought using
    WorksheetFunction.CountA(Range("A1:A10"))
    Could help finding empty cells in a range, just replace A1 and A10 with other fields.
    CountA, counts all empty fields in the range so you could do something like this:
    x = WorksheetFunction.CountA(Range("A1:A10"))
    if x = 0 Then
    'Something
    Else
    'Something
    End if

    I hope my reply is somehow usefull.

  5. #5
    Join Date
    Sep 2006
    Posts
    60
    Thanks for your interest and reply. I've finally ( I believe ) found an algorithm that seems to work, after several weeks of frustration and trial/error.
    For what it's worth, the code below acts on a previous page of check boxes and true/false logic elsewhere. But, it seems to consistenly flag me for an empty range/empty cell within the range.

    Dim c As Range

    On Error Resume Next
    For Each c In [I13:I68]

    If c.EntireRow.Hidden <> True Then 'flag for empty cell/range of cells
    If IsEmpty(c) Then
    MsgBox "YOU HAVE NOT COMPLETED A JUSTIFICATION. PLEASE ENTER A JUSTIFICATION", vbOKOnly, "IV&V Error Alert"
    Exit Sub
    End If
    End If
    Next

    For Each c In [I13:I68]
    If c.EntireRow.Hidden <> True Then 'successful entry of ALL cells
    If Not IsEmpty(c) Then
    'then advance to next screen
    sSheetTab = sSheetTabName(wkbBook, gsSHEET_LABOR_HOURS)
    Set wksSheet = wkbBook.Worksheets(sSheetTab)
    'activate sheet and make visible
    wksSheet.Activate
    wksSheet.Visible = True
    Exit Sub
    End If
    End If

    Next

    For Each c In [I13:I68]
    If c.EntireRow.Hidden = True Then 'no justifications, so pass on to next page
    'If Not IsEmpty(c) Then
    'then advance to next screen
    sSheetTab = sSheetTabName(wkbBook, gsSHEET_LABOR_HOURS)
    Set wksSheet = wkbBook.Worksheets(sSheetTab)
    'activate sheet and make visible
    wksSheet.Activate
    wksSheet.Visible = True
    Exit Sub
    ' End If
    End If
    Next

    Case gsSHEET_LABOR_HOURS ' the next page, if successful
    End Select



    For those of you who are curious, this code checks to see that ALL cells are entered before going on to the next page. If they are, advance; if NO justifications are checked from the previous page (a BIG distinction from NO spaces entered).

    Note that it mentions hidden rows - that's because logic not mentioned here is hiding rows that weren't checked from the previous page's logic. So, in essence, it checks for ONLY non-hdden rows, something I found from a web page on hidden rows (sorry, I forgot where, just do a Google on hidden rows, you'll find it) and modified the little algorithm into my project.

    Hope somebody can find something useful here!
    Last edited by mike21152; 09-29-07 at 17:33.

  6. #6
    Join Date
    Sep 2006
    Posts
    60
    Just a postscript: If anyone wants to see how the above code works, you can do what I did to prove the concept right - just take a simple spreadsheet and fill up the rows A1:A5 with data (whatever you want). Hide rows A6:A10. Now apply the code without all the stuff I have to get to the next page. Just put a msgbox somewhere to prove that you filled in the rows, or got some blank row error msgs.

    Oh, yeah - it works even if you only have SOME blanks and SOME filled.
    Last edited by mike21152; 09-30-07 at 20:33.

Posting Permissions

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