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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

ChrisOswald

Active Member
Joined
Jan 19, 2010
Messages
454
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:

colmguckian

New Member
Joined
Feb 11, 2011
Messages
35
Thanks Chris
I used your code, but it is not finding the text in ComboBox3
Im not sure why?
 

ChrisOswald

Active Member
Joined
Jan 19, 2010
Messages
454
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.
 

colmguckian

New Member
Joined
Feb 11, 2011
Messages
35

ADVERTISEMENT

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

Active Member
Joined
Jan 19, 2010
Messages
454
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,159
Messages
5,527,149
Members
409,749
Latest member
esmarques

This Week's Hot Topics

Top