find text within columns - vba

lordriel

Board Regular
Joined
Nov 1, 2005
Messages
68
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I would think something structured along these lines should work for you...
Code:
  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
 
Upvote 0
"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.
 
Upvote 0
considered the double-search, or allow the user to indicate if it was a full or partial item
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top