Execute macro unexpected results

Help
,
1

I have a macro called “vin_search” in an excel file with the following code

Sub vin_search(Optional vin As String)
ActiveWorkbook.Sheets("BLANK").Activate
Dim WS_Count As Integer
WS_Count = ActiveWorkbook.Sheets.Count
Dim Z As Integer

For Z = 1 To WS_Count

ActiveWorkbook.Worksheets(Z).Activate
    
Dim Cell As Range
Columns("C:C").Select
Set Cell = Selection.Find(What:=vin, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If Cell Is Nothing Then

Else
    MsgBox ("Found")
    MsgBox (Cells(Cell.Row, Cell.Column + 1).Value)
    Exit For
End If
Next Z

End Sub

When I call it with Execute Macro it never finds the expected vin, however when I run it directly through excel, no problems. Any thoughts?

2

hi @mkkajtez

Can you give it like this
vin_search(vin)

Thanks
ashwin S

3

Please update the code as below and see if that works.

Sub vin_search(Optional vin As String)
ThisWorkbook.Sheets("BLANK").Activate
Dim WS_Count As Integer
WS_Count = ThisWorkbook.Sheets.Count
Dim Z As Integer
Dim wks As WorkSheet

For Z = 1 To WS_Count

Set wks = ThisWorkbook.Worksheets(Z)
wks.Activate
    
Dim Cell As Range
Set Cell = wks.Columns("C:C").Find(What:=vin, After:=wks.Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If Cell Is Nothing Then

Else
    MsgBox ("Found")
    MsgBox (wks.Cells(Cell.Row, Cell.Column + 1).Value)
    Exit For
End If
Next Z
Set wks = Nothing
End Sub

Regards,
Karthik Byggari

4 Likes
4

I seem to be getting a type mismatch at the Set Cell = wks.Columns part. I tried your suggesstion with the other parts of code but it still didn’t work however.