Search for a value in a workbook

colmguckian

New Member
Joined
Feb 11, 2011
Messages
35
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
 

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 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.

Code:
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
 
Last edited:
Upvote 0
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
Code:
[COLOR=#ff0000]With WS.Range("A1:D500")[/COLOR]
with
Code:
[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.
 
Upvote 0
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?
 
Upvote 0
Just got around to checking your reply: see comments in code for ... comments on the code

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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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