Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Apr 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validate Loop (Excel 97)

    I want to run a macro which after entering the data in an inputbox, it will check all the data that is listed in column A if it matches with what was entered in the inputbox. If there is a match to have a message that indicates this and display the cell from column B that is next to the column A cell that is a match.

    Thanks for any assistance.

  2. #2
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validate Loop (Excel 97)

    Hi There

    Not a macro, a single cell formula:

    =INDEX(A118:A127,(MATCH(D125,B118:B127,0)))

    Where:

    A118:A127 is the column from which you want to display the value. (This is B in your question)
    D125 is the cell you have entered the search/new value in.
    B118:B127 is the column against which you want to check for the value entered.

    Hope this helps some.

    Regards
    Peter

  3. #3
    Lounger
    Join Date
    Apr 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validate Loop (Excel 97)

    Thanks for your help. But if I wanted to run a macro would it be possible?

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

    Re: Validate Loop (Excel 97)

    I think this does what you asked:

    <pre>Public Sub CkeckList()
    Dim strInput As String
    Dim I As Long, lLast As Long
    strInput = InputBox("Enter data")
    If strInput = "" Then Exit Sub
    lLast = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
    For I = 0 To lLast
    If Worksheets("Sheet1").Range("A1").Offset(I, 0).Value = strInput Then
    MsgBox "Found " & strInput & " Value is " & Worksheets("Sheet1").Range("A1").Offset(I, 1).Value
    Exit For
    End If
    Next I
    End Sub
    </pre>

    Legare Coleman

  5. #5
    Lounger
    Join Date
    Apr 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validate Loop (Excel 97)

    Thank-you.

  6. #6
    Lounger
    Join Date
    Apr 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validate Loop (Excel 97)

    What would I write into the macro so that if it doesn't equal any of the accounts to have a message box indicate that it is an invalid entry?

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

    Re: Validate Loop (Excel 97)

    Here is Legare Coleman's code with some additions (in bold):

    Public Sub CheckList()
    Dim strInput As String
    Dim I As Long, lLast As Long
    Dim blnFound As Boolean
    strInput = InputBox("Enter data")
    If strInput = "" Then Exit Sub
    lLast = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
    For I = 0 To lLast
    If Worksheets("Sheet1").Range("A1").Offset(I, 0).Value = strInput Then
    MsgBox "Found " & strInput & " Value is " & Worksheets("Sheet1").Range("A1").Offset(I, 1).Value
    blnFound = True
    Exit For
    End If
    Next I
    If Not blnFound Then
    MsgBox "Invalid Entry!"
    End If

    End Sub

  8. #8
    Lounger
    Join Date
    Apr 2002
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validate Loop (Excel 97)

    Thanks for all the help everyone.

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

    Re: Validate Loop (Excel 97)

    I would do it a little simpler than Hans <img src=/S/smile.gif border=0 alt=smile width=15 height=15> :

    <pre>Public Sub CkeckList()
    Dim strInput As String
    Dim I As Long, lLast As Long
    strInput = InputBox("Enter data")
    If strInput = "" Then Exit Sub
    lLast = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
    For I = 0 To lLast
    If Worksheets("Sheet1").Range("A1").Offset(I, 0).Value = strInput Then
    MsgBox "Found " & strInput & " Value is " & Worksheets("Sheet1").Range("A1").Offset(I, 1).Value
    Exit For
    End If
    Next I
    If I > lLast then
    MsgBox "Data entered not in list."
    End If
    End Sub
    </pre>

    Legare Coleman

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

    Re: Validate Loop (Excel 97)

    Hello Legare,

    I admit that I use that trick myself, but I always feel a bit uncomfortable about it - it relies on the way a For...Next loop is implemented in VB. In some other languages, the loop index is undefined as soon as you exit the loop.
    Besides, the - admittedly more cumbersome - technique of setting a flag can be applied in other situations, so I thought it would be more educative.

    But it's nice to demonstrate different ways of solving a problem in a thread.

Posting Permissions

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