Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    multiple inputs (2003)

    My spreadsheet contains sales information for products. Some products have specials, denoted by a column that may contain a single-character code if that product has a special deal associated with it. My code asks the user what specials they want to keep, if any, and if they enter a letter then all the other rows which have special deals

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

    Re: multiple inputs (2003)

    Change the line

    If ocell.Value <> msgin Then

    to

    If InStr(msgIn, oCell.Value) > 0 Then

    This tests if the single-character entry in oCell occurs in the string msgIn entered by the user.

    Note: you can omit the test If ocell.Value <> "" Then

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple inputs (2003)

    Hans,
    Thank you for such an elegant solution to my problem.
    Greatly appreciated.
    Have a great Christmas.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple inputs (2003)

    Hi - OK, the above code is great to delete the row it has found (ie:

    If InStr(msgin, ocell.Value) > 0 Then 'ie if the value entered is in the cell
    ocell.EntireRow.Delete Shift:=xlUp 'then delete the row

    but what if I want to keep only these rows? That is, delete the blank rows and any rows that have any other letter in them?

    I tried
    If InStr(msgin, ocell.Value) <> 0 Then

    but no rows were deleted, so how do I do this?

    Thanks again!

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple inputs (2003)

    does this work:

    InStr(msgin, ocell.Value) =0

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple inputs (2003)

    Hi Paul,
    Thanks for your reply. Unfortunately it just trips and matches nothing, so it zooms past the code deleting no rows at all.

  7. #7
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple inputs (2003)

    Ted
    I'm currently working on a machine with xl 97 prior to the introduction of the InStr function so I can't look it up. However, if I remember correctly doesn't it return the relative position of the character ( in your case the oCell.value) inside of the target string (in this case, the msgin string? If, the character is NOT found in msgin, then you want to delete that row, correct? So whatever is returned ( 0 or #NA) should be what you are looking for. Perhaps you can check the function help and get your answer.

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

    Re: multiple inputs (2003)

    Try this:

    Dim lngRow As Long
    For lngRow = ActiveSheet.Range("SPECIALS").Rows.Count To 1 Step -1
    Set oCell = ActiveSheet.Range("SPECIALS").Cells(lngRow, 1)
    If InStr(msgIn, oCell.Value) = 0 Then
    oCell.EntireRow.Delete
    End If
    Next lngRow

  9. #9
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple inputs (2003)

    Thanks Hans, works perfectly.
    Thanks also others for your help.

Posting Permissions

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