Results 1 to 3 of 3

Thread: Lookup() (97)

  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Lookup() (97)

    I am currently working on a data submission worksheet that users have to enter data on a monthly basis. To automate it I have added a submit button that fires it off to the data collator. This is no problem. The problem is that the data collator has found errors of the submitted workbooks.

    The sheet contains a few columns that should all add up correctly. A simple if statement resolves the problem by stating Data Error against the corresponding row. This is copied down the length of a column from F12:F55.

    What I want to do is put an error handler in my code for the submit button that checks the range F12:F55 and if one of the cells contains Data Error then a message box tells them that something needs checking.

    To do this I put Lookup("Data Error", F12:F55) in a cell and then got my code to reference that cell to check. I thought "quick fix, simple Jerry", wrong. The lookup doesn't work properly. It is driving me mad.

    Can anyone suggest a way that I can check the values of cells F12:55 to see if they contain Data Error
    Jerry

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookup() (97)

    Does this code snippet help get you started? It checks the range and list the cells that have errors

    Steve

    <pre>Sub LookupErrors()
    Dim rng As Range
    Dim iErrCount As Integer
    Dim rcell As Range
    Dim bError As Boolean
    Dim sErrMsg As String
    sErrMsg = ""
    iErrCount = 0
    Set rng = Range("F12:F55")
    For Each rcell In rng
    If rcell.Value = "Data Error" Then
    sErrMsg = sErrMsg & vbCrLf & rcell.Address
    iErrCount = iErrCount + 1
    End If
    Next rcell
    If iErrCount > 0 Then
    sErrMsg = "I found " & iErrCount & " Error" & _
    IIf(iErrCount = 1, ". It is in Cell:", _
    "s. They are in cells:") & sErrMsg

    Else
    sErrMsg = "I found no errors"
    End If
    MsgBox sErrMsg
    End Sub</pre>


  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Lookup() (97)

    Steve

    This has certainly got me started. It is always the <img src=/w3timages/censored.gif alt=censored border=0> Range function I forget about.

    Do you know if there is a IsForgot function? <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> as I will be able to work that a treat.

    Thanks again, it now works brilliantly
    Jerry

Posting Permissions

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