find text within columns - vba

[lordriel]

I am working on a small macro that I know I've done before, but for some reason am stumped over.

I have six columns - Part1, Desc1, and Tie, and Part2, Desc2, and Info. (Items/descriptions in Part1/Desc1 will not be repeated in Part2/Desc2.) All data, numerical or otherwise, is treated as a text string.

I have set up an input screen for the user to input part or all of the Part number; the code I want to write will search, first, Part1, then Part2, and display either the tie-hi or the info listed for that part.

End result - when the user opens the workbook, an input box pops up - they key in the part or description information, and a display will come up with the info. They then have the option to close or start a new search.

Key is, the user may know only part of the item number or description, so the search results will include all similar part numbers.

I know the function is fairly simple, but either I'm not wrapping my head around the right information or I'm looking in the wrong direction entirely.

Any help would be greatly appreciated.

 

[Rick Rothstein]

I would think something structured along these lines should work for you...

  Dim LastRow As Long, InputFromUser As String, Cell As Range
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  [B][COLOR="#0000FF"]InputFromUser[/COLOR][/B] = InputBox("What do you want to find?")
  If Len([B][COLOR="#0000FF"]InputFromUser[/COLOR][/B]) Then
    Set Cell = Columns("A:C").Find("*" & [B][COLOR="#0000FF"]InputFromUser[/COLOR][/B] & "*", MatchCase:=False)
    If Cell Is Nothing Then
      Set Cell = Columns("D:F").Find("*" & [B][COLOR="#0000FF"]InputFromUser[/COLOR][/B] & "*", MatchCase:=False)
    End If
    If Not Cell Is Nothing Then
[B][COLOR="#008000"]      '
      ' Cell is now a reference to the found cell, so you can
      ' retrieve and information from the row that it is on
      '[/COLOR][/B]
    End If
  End If

 

[gbutton]

"Key is, the user may know only part of the item number or description, so the search results will include all similar part numbers."

This is what you want to happen and it's not happening, or this is what's happening and you don't want it to happen?

In the first case use [SEARCH STRING] = "*" & [SEARCH STRING] &"*" to add wildcards on either end of the search. This should return partial matches, but might also cause issues when they have the exact number.

You might run the search twice. Once with the exact string, no wild cards, and again if there's no exact match.

 

[lordriel]

considered the double-search, or allow the user to indicate if it was a full or partial item

 

[lordriel]

ADVERTISEMENT

Rick - how do I reference the cell in the next column? i.e. I look for '343', it finds it, I want to display the tie-hi or info in the next column

 

[Rick Rothstein]

Rick - how do I reference the cell in the next column? i.e. I look for '343', it finds it, I want to display the tie-hi or info in the next column

If the Cell variable gets set to a particular cell reference, then Cell.Row will tell you what row it is on. You can then use this to address any particular column on that row... for example, if the "info" column is Column F, then this would give you the value in that column for the found row number...

InfoValue = Cells(Cell.Row, "F").Value

alternately (useful if your are iterating the columns), the "F" can be replaced by 6 (the actual column number), so this is exactly equivalent to the above code line...

InfoValue = Cells(Cell.Row, 6).Value

or, if the 6 (or "F") is held in a variable named, say, Col, then this...

InfoValue = Cells(Cell.Row, Col).Value

 

[lordriel]

I believe this is working the way I wanted it to. Not the way I've done it in the past, but - since I can't remember exactly what I did - that matters little.

Thanks Rick and gbutton for the responses and help!