Thread: Problem in searching for a text value in a range

Hi,

I have a userform wherein I have a dropdown list. On selection of a value in that dropdown list, I want to search for that value in the worksheet and display the value of the corresponding cell in a textbox in the userform. For that, first I want to find that cell where searched value is, and then using the Offset property I would pick the value of the corresponding cell. Unfortunately, I am unable to find the text in the given range in the first place. Here's the code that I had attempted but no result is shown:

Dim searchTxt As String
Dim wb As Workbook
Dim ws As Worksheet
Dim oRng, sRng As Range
Dim finalRow As Long

searchTxt = Trim(comboBox1.Value)
Set wb = ThisWorkbook
With wb
    Set ws = .Worksheets("Sheet1")
End With

finalRow = ws.Range("A1", "A65536").End(xlUp).Row
Set sRng = ws.Range("B1:B" & finalRow)
Set oRng = sRng.Find(What:=searchTxt, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False)
If Not oRng Is Nothing Then
    textBox1.Value = oRng
    Else
        MsgBox "No results found."
End If

Please help.

Peace,

Shivboy

Hi Antonio,

Thanks a lot for replying. I tried the code you suggested, but in vain. Although there is data in columns A & B, it still shows the oRng object as empty. Why is that happening? Please help.

Note: Check the If....Else condition.

Peace,

Shivboy

I'm sorry but in my previous code I wrote 'foglio1' rather than 'sheet1'. Please replace this code:

   With wb
       Set ws = .Worksheets("foglio1")
   End With

with:

   With wb
       Set ws = .Worksheets("Sheet1")
   End With

If it still doesn't run please attach a little example of your file.

Regards,
Antonio

Originally Posted by antoka05

Try to change the code marked in bold (if you need to find for data in column 'A' and take the value of column 'B' in the same row of found data):

   Dim searchTxt As String
   Dim wb As Workbook
   Dim ws As Worksheet
   Dim oRng, sRng As Range
   Dim finalRow As Long
   
   searchTxt = Trim(comboBox1.Value)
   Set wb = ThisWorkbook
   With wb
       Set ws = .Worksheets("foglio1")
   End With
   
   finalRow = ws.Cells(ws.Rows.Count, "a").End(xlUp).Row
   Set sRng = ws.Range("a1:a" & finalRow)
   Set oRng = sRng.Find(What:=searchTxt, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False)
   If Not oRng Is Nothing Then
      textBox1.Value = oRng.Offset(0, 1).Value 'To take 'B' column value
      Else
         MsgBox "No results found."
   End If

Hope it's what you need.

Regards,
Antonio

What Antonio suggested should work If it isn't I have two suggestins to debug. Use msgbox to check if your combobox is actually returning the value you are expecting. eg

searchTxt = Trim(comboBox1.Value)
msgbox searchTxt
...

If the active cell is beyond the cell where you know the value exists then search may not find the cell as search starts from the active cell. Might be a good idea to activate the first cell of the range before you start search eg add

Range("B1").Activate
Set oRng = sRng.Find....

Also I noticed a slight descripancy. What you did was decide last row of the range by taking last cell of col A, search col B for the data and write this
data to text box. Antonio is doing a little different. He is also taking the last used cell of col A to decide the finalrow of the range but the range he is searching for data is col A and if he finds the data in any cell in col A he takes data from corresponding cell in col B and writes to text box. Just check what is your actual requirement.

A V Veerkar