Search for a value in a workbook

[colmguckian]

I have a macro which
Searches for a value in an inactive workbook. It searches for the value which is selected in ComboBox3. The macro I have does this, but it only searches Sheet1. I need it to search all the worksheets in the Workbook
Please see code below.

Dim wFind as Range
With Workbooks("book1").Worksheets("Sheet1").Range("A1:D500")
Set wFind = .Find(What:=Me.ComboBox3.Value, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not wFind Is Nothing Then
Me.TextBox1.Value = wFind.Offset(, 7)
Else: TextBox1 = Empty
End If
End with

 

[ChrisOswald]

I coulda sworn that .find had a setting to check the whole workbook, but I couldn't find it with either help or the macro recorder, so here's a code adjustment instead.

Sub TryThis()
    Dim wFind                       As Range
[COLOR=red]    Dim WS                          As Worksheet[/COLOR]
    [COLOR=red]With Workbooks("book2")[/COLOR]
[COLOR=red]      For Each WS In .Worksheets[/COLOR]
[COLOR=red]          With WS.Range("A1:D500")[/COLOR]
 
                Set wFind = .Find(What:=Me.ComboBox3.Value, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
                If Not wFind Is Nothing Then
                    Me.TextBox1.Value = wFind.Offset(, 7)
                Else: TextBox1 = Empty
                End If
 
[COLOR=red]          End With[/COLOR]
[COLOR=red]      Next WS[/COLOR]
 
    End With
End Sub

 

[colmguckian]

Thanks Chris
I used your code, but it is not finding the text in ComboBox3
Im not sure why?

 

[ChrisOswald]

Well, here's a couple of structural things to look into first.

1) Is the value you're searching for within the range "A1:D500" on any of the sheets within your workbook? If that's the problem, and you want to search each sheet exhaustively, replace

[COLOR=#ff0000]With WS.Range("A1:D500")[/COLOR]

with

[COLOR=#ff0000]With WS.Cells[/COLOR]

2) Is there any extra spaces with the combobox value and/or the cell value you're looking for?

I'll admit that I didn't test this out with a userform with controls named like yours (or in a userform at all ), but I don't think that should be the source of the problem.

 

[colmguckian]

I have checked all your suggestions. None of these are the problem.
The macro only seems to return a value when, the matching value with ComboBox3, is on the last sheet. If the value is on any other sheet, it wont find it. Do you know why this could be?

 

[ChrisOswald]

Just got around to checking your reply: see comments in code for ... comments on the code

Sub TryThis()
'NOTE:  This sub update the textbox value to the first found value on the
'last sheet that the ComboBox value is found.
'For example, if
'ComboBox Value is "xyz";
'sheet1.range("A1").value = "xyz"
'sheet2.range("B1").Value = "xyz"
'sheet2.range("C3").value = "xyz"
'then the textbox will be updated in regards to
'the value in sheet2.range("B1") -- which is
'sheet2.range("I1").value
 
'However, please note that Range("A1") is the last value searched.
'There's an implicit paramater in the .find statement saying
'"start searching AFTER range ("A1").
'this can be fixed by explicitly setting the parameter to the last cell
'in the search range via adding.
'After:=[D500]
'(see change in the .Find line below)
'If you have concerns regarding choosing which of multiple matches should be returned,
'there'll need to be some more work to be done on this
    Dim wFind                       As Range
    Dim WS                          As Worksheet
 
    'initialize textbox value to empty string.
    '(to prevent a value found on a preveious search
    'returning if nothing is found on the new search)
 
    Me.TextBox1.Value = Empty
    'Depending on how you're using this, you might want to
    'change ActiveWorkbook to the actual workbook you're using.
    With ActiveWorkbook
        For Each WS In .Worksheets
            With WS.Range("A1:D500")
                Set wFind = .Find(What:=Me.ComboBox3.Value, LookAt:=xlWhole, _
                                  After:=[D500], MatchCase:=False, SearchFormat:=False)
                If Not wFind Is Nothing Then
                    Me.TextBox1.Value = wFind.Offset(, 7)
                    'removing the else clause:  this is why it was only finding
                    'values on the last sheet.  (if there isn't a value on the last sheet,
                    'then wFind is Nothing, and the else clause will fire)
                    'Else: TextBox1 = Empty
                End If
            End With
        Next WS
    End With
End Sub

 

[colmguckian]

Thanks Chris, It now works very well
Thanks for your detailed comments.