Unexplained/Unknow"Object variable or With block variable not set"

[Wellie]

I have the following codes to scan a list of items from the "Sourc_Tab"
against "Target_SW_TAB". The alogrithm is go thru on cell at a time in
Column F of the "Source_Tab". Everytime, it picks up a name (saved in
varable 'TargetName'). It then switches to the "Target_SW_TAB" and uses the
Find() function to find if the name exists. if found, returns to
"Source_Tab" and does something. Otherwise, skip to next name. please note
that I restart the search on "Target_SW_TAB" from cell "a2".

After some random number of findings, it boomed and prompted me an error
saying that "Object variable or With block variable not set" and it STOPs at
the Find() statement below. I CANNOT figure out what is wrong with the code
below.

Can someone please help ?

Public Sub Get_SW_Version_Info(Source_Tab As String)
Dim TargetName As Variant
Dim Matched As Boolean
Dim Found As Boolean
Dim Status_Info As String
Dim i As Integer

' Loop thru target list
Worksheets(Source_Tab).Activate
Range("f2").select

Do While Not IsEmpty(ActiveCell.Value)
TargetName = ActiveCell.Value
Status_Info = "*** Matching '" + TargetName + "' "
Worksheets(TARGET_SW_TAB).Activate
Worksheets(TARGET_SW_TAB).Range("a2").Select
Application.StatusBar = Status_Info
Found = False
On Error GoTo WS_Not_Exist
' After the first round, encounter Run Time Err 91 - Object variable
or With Block variable not set
Cells.Find(What:=TargetName, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select
On Error GoTo 0
' Found the software in the Software List tab
Application.StatusBar = Status_Info + " with '" + ActiveCell.Value
+ "' of row " + CStr(ActiveCell.Row)
'Selection.Font.Bold = True
'Selection.Font.ColorIndex = 46 ' Set text to Orange
RowN = ActiveCell.Row
Found = True
' Return to the source tab
Worksheets(Source_Tab).Activate
ActiveCell.Offset(0, 7).Select
ActiveCell.Formula = "='" & TARGET_SW_TAB & "'!F" & CStr(RowN)
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "='" & TARGET_SW_TAB & "'!G" & CStr(RowN)

WS_Not_Exist:
Worksheets(Source_Tab).Activate
If Found Then
ActiveCell.Offset(1, -8).Select
Else
ActiveCell.Offset(1, 0).Select
End If

Loop

HDG_NOT_FOUND:
MsgBox "heading '" + SW_NAME_HDR + "' not found. Aborted!", vbCritical

End Sub

[Die_Another_Day]

that means it did not find what you wanted to find. try this:
Dim fCell as Range 'Finder Cell
Set fCell = Cells.Find(What:=TargetName, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If fCell is Nothing then
'Your code to handle missing data
Else
'Your other code
End If

HTH

Charles Chickering

Wellie wrote:
> I have the following codes to scan a list of items from the "Sourc_Tab"
> against "Target_SW_TAB". The alogrithm is go thru on cell at a time in
> Column F of the "Source_Tab". Everytime, it picks up a name (saved in
> varable 'TargetName'). It then switches to the "Target_SW_TAB" and uses the
> Find() function to find if the name exists. if found, returns to
> "Source_Tab" and does something. Otherwise, skip to next name. please note
> that I restart the search on "Target_SW_TAB" from cell "a2".
>
> After some random number of findings, it boomed and prompted me an error
> saying that "Object variable or With block variable not set" and it STOPs at
> the Find() statement below. I CANNOT figure out what is wrong with the code
> below.
>
> Can someone please help ?
>
> Public Sub Get_SW_Version_Info(Source_Tab As String)
> Dim TargetName As Variant
> Dim Matched As Boolean
> Dim Found As Boolean
> Dim Status_Info As String
> Dim i As Integer
>
> ' Loop thru target list
> Worksheets(Source_Tab).Activate
> Range("f2").select
>
> Do While Not IsEmpty(ActiveCell.Value)
> TargetName = ActiveCell.Value
> Status_Info = "*** Matching '" + TargetName + "' "
> Worksheets(TARGET_SW_TAB).Activate
> Worksheets(TARGET_SW_TAB).Range("a2").Select
> Application.StatusBar = Status_Info
> Found = False
> On Error GoTo WS_Not_Exist
> ' After the first round, encounter Run Time Err 91 - Object variable
> or With Block variable not set
> Cells.Find(What:=TargetName, After:=ActiveCell, LookIn:=xlFormulas,
> LookAt _
> :=xlWhole, SearchOrder:=xlByRows,
> SearchDirection:=xlNext, MatchCase:= _
> False, SearchFormat:=False).Select
> On Error GoTo 0
> ' Found the software in the Software List tab
> Application.StatusBar = Status_Info + " with '" + ActiveCell.Value
> + "' of row " + CStr(ActiveCell.Row)
> 'Selection.Font.Bold = True
> 'Selection.Font.ColorIndex = 46 ' Set text to Orange
> RowN = ActiveCell.Row
> Found = True
> ' Return to the source tab
> Worksheets(Source_Tab).Activate
> ActiveCell.Offset(0, 7).Select
> ActiveCell.Formula = "='" & TARGET_SW_TAB & "'!F" & CStr(RowN)
> ActiveCell.Offset(0, 1).Select
> ActiveCell.Formula = "='" & TARGET_SW_TAB & "'!G" & CStr(RowN)
>
> WS_Not_Exist:
> Worksheets(Source_Tab).Activate
> If Found Then
> ActiveCell.Offset(1, -8).Select
> Else
> ActiveCell.Offset(1, 0).Select
> End If
>
> Loop
>
> HDG_NOT_FOUND:
> MsgBox "heading '" + SW_NAME_HDR + "' not found. Aborted!", vbCritical
>
> End Sub