Private Sub userform3OK_Click()
Dim Findit As Object, CF As Object
For Each Wks In Worksheets
Set CF = Wks.UsedRange.Cells
Set Findit = CF.Find(What:=ComboBox1.Value, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext,MatchCase:=False, SearchFormat:=False)
If Not Findit Is Nothing Then MsgBox Wks.Name
Next Wks
End Sub


Can anyone tell me where the subject line error is is the above code?

Corey....

Corey,

Unless you haave "Wks" defined as a global level variable, you will need to do the following:

Dim Wks as Worksheet
Set Wks = Worksheets("Sheet1") ' replace Sheet1 with your worksheet name

' When you change sheets, reset Wks to point to the new one

Set Wks = Worksheets("Sheet2") ' and so on through all your sheets.

Also instead of defining CF as object, you might want to define it specifically as a Range. I have never played with using a Find like that. I probably would have used a couple of For loops such as:

Dim iRow as Integer
Dim iCol as Integer
Dim bFound as Boolean

For iRow = 1 to ActiveSheet,UsedRange.Rows.Count
For iCol = 1 to ActiveSheet.UsedRange.Columns.Count
If Cells(iRow,iCol).Value = ComboBox1.Value Then
bFound = True
' Do other things here if you want to continue searching...
Exit For ' Leave out if search is to continue
End If
Next iCol
If iFound = True Then Exit For ' Leave out if search is to continue
DoEvents ' Keep Windows Happy

Next iRow

If iFound = True Then
MsgBox Wks.Name
' Other Stuff if Necessary
End IF

Then, if searching multiple sheets you can wrap the entire thing into another loop to go through each worksheet.
"Corey" wrote in message ...
Private Sub userform3OK_Click()
Dim Findit As Object, CF As Object
For Each Wks In Worksheets
Set CF = Wks.UsedRange.Cells
Set Findit = CF.Find(What:=ComboBox1.Value, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext,MatchCase:=False, SearchFormat:=False)
If Not Findit Is Nothing Then MsgBox Wks.Name
Next Wks
End Sub


Can anyone tell me where the subject line error is is the above code?

Corey....

I agree Wks s/b declared (as it is a good practice),

Dim Wks As Worksheet

however, if the OP's not using option explicit, vba will treat it as a
variant and the code will still run. If he was using option explicit, he'd
get a compile error stating "variable not defined", not a run-time error.

I would ask the OP to double check the name of the combobox. Is it actually
named combobox1 or did he change it and forget to change the code? His code
runs fine on my machine (I have to remove SearchFormat:=False as I have XL
2000). Also, I would qualify Combox1 w/the name of the form, but VBA didn't
seem to mind.





"Dove" wrote:

Corey,

Unless you haave "Wks" defined as a global level variable, you will need to do the following:

Dim Wks as Worksheet
Set Wks = Worksheets("Sheet1") ' replace Sheet1 with your worksheet name

' When you change sheets, reset Wks to point to the new one

Set Wks = Worksheets("Sheet2") ' and so on through all your sheets.

Also instead of defining CF as object, you might want to define it specifically as a Range. I have never played with using a Find like that. I probably would have used a couple of For loops such as:

Dim iRow as Integer
Dim iCol as Integer
Dim bFound as Boolean

For iRow = 1 to ActiveSheet,UsedRange.Rows.Count
For iCol = 1 to ActiveSheet.UsedRange.Columns.Count
If Cells(iRow,iCol).Value = ComboBox1.Value Then
bFound = True
' Do other things here if you want to continue searching...
Exit For ' Leave out if search is to continue
End If
Next iCol
If iFound = True Then Exit For ' Leave out if search is to continue
DoEvents ' Keep Windows Happy

Next iRow

If iFound = True Then
MsgBox Wks.Name
' Other Stuff if Necessary
End IF

Then, if searching multiple sheets you can wrap the entire thing into another loop to go through each worksheet.
"Corey" wrote in message ...
Private Sub userform3OK_Click()
Dim Findit As Object, CF As Object
For Each Wks In Worksheets
Set CF = Wks.UsedRange.Cells
Set Findit = CF.Find(What:=ComboBox1.Value, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext,MatchCase:=False, SearchFormat:=False)
If Not Findit Is Nothing Then MsgBox Wks.Name
Next Wks
End Sub


Can anyone tell me where the subject line error is is the above code?

Corey...

I didn't fire up Excel to test what the error message would be, but as a
habit I always use Option Explicit and avoid using variants whenever
possible.

I didn't think of the possibility of changing the combo name... Having
programmed in VB for some time I have gotten in the habit of using a
meaningful name for all UI controls and a prefix for the type of control...

Also, clicking on the debug button when the error comes up (since it is a
runtime vs compile error) should point to the exact spot in the code where
the error is/was...

David

however, if the OP's not using option explicit, vba will treat it as a
variant and the code will still run. If he was using option explicit,
he'd
get a compile error stating "variable not defined", not a run-time error.

I would ask the OP to double check the name of the combobox. Is it
actually
named combobox1 or did he change it and forget to change the code? His
code
runs fine on my machine (I have to remove SearchFormat:=False as I have XL
2000). Also, I would qualify Combox1 w/the name of the form, but VBA
didn't
seem to mind.