Nulls within a .Find command
 

Here is the code:

With Workbooks("Workbookname.xls").Sheets("Total").Rows (1)

ColToFind = .Find(What:="What I am looking for",
After:=.Cells(.Columns.Count), _
SearchOrder:=xlByRows).Column

End With

The problem is sometimes ColToFind is not going to be there. I need a way
to tell it that if ColToFind comes back null then do not continue the module.

Nulls within a .Find command
 

I added

On Error Resume Next

Above the code and it seemed to work. Is this a good fix?

"Kevin Porter" wrote:

Here is the code:

With Workbooks("Workbookname.xls").Sheets("Total").Rows (1)

ColToFind = .Find(What:="What I am looking for",
After:=.Cells(.Columns.Count), _
SearchOrder:=xlByRows).Column

End With

The problem is sometimes ColToFind is not going to be there. I need a way
to tell it that if ColToFind comes back null then do not continue the module.

Nulls within a .Find command
 

hi,
based on your first post, not really. the resume next command will continue
executing code. you said you wanted code to stop if null.
try this...
after the with clause...
if ColToFind is nothing then
msgbox("not found")
exit sub
end if

If ColToFind is null, code should stop. (untested)
if ColToFind is not null, code should continue

Regards
FSt1
"Kevin Porter" wrote:

I added

On Error Resume Next

Above the code and it seemed to work. Is this a good fix?

"Kevin Porter" wrote:

Here is the code:

With Workbooks("Workbookname.xls").Sheets("Total").Rows (1)

ColToFind = .Find(What:="What I am looking for",
After:=.Cells(.Columns.Count), _
SearchOrder:=xlByRows).Column

End With

The problem is sometimes ColToFind is not going to be there. I need a way
to tell it that if ColToFind comes back null then do not continue the module.

Nulls within a .Find command
 

What should I DIM ColToFind as? I had it as LONG as I am looking for a name.
But I get and error that it is mismatched with the nothing qualifier.

"FSt1" wrote:

hi,
based on your first post, not really. the resume next command will continue
executing code. you said you wanted code to stop if null.
try this...
after the with clause...
if ColToFind is nothing then
msgbox("not found")
exit sub
end if

If ColToFind is null, code should stop. (untested)
if ColToFind is not null, code should continue

Regards
FSt1
"Kevin Porter" wrote:

I added

On Error Resume Next

Above the code and it seemed to work. Is this a good fix?

"Kevin Porter" wrote:

Here is the code:

With Workbooks("Workbookname.xls").Sheets("Total").Rows (1)

ColToFind = .Find(What:="What I am looking for",
After:=.Cells(.Columns.Count), _
SearchOrder:=xlByRows).Column

End With

The problem is sometimes ColToFind is not going to be there. I need a way
to tell it that if ColToFind comes back null then do not continue the module.

Nulls within a .Find command
 

Dim FoundCell as Range
dim ColToFind as long

With Workbooks("Workbookname.xls").Sheets("Total").Rows (1)
set foundcell = .Find(What:="What I am looking for", _
After:=.Cells(.Columns.Count), SearchOrder:=xlByRows)
end with

if foundcell is nothing then
Coltofind = 99999 'what do you want?
else
coltofind = foundcell.column
end if


Kevin Porter wrote:

Here is the code:

With Workbooks("Workbookname.xls").Sheets("Total").Rows (1)

ColToFind = .Find(What:="What I am looking for",
After:=.Cells(.Columns.Count), _
SearchOrder:=xlByRows).Column

End With

The problem is sometimes ColToFind is not going to be there. I need a way
to tell it that if ColToFind comes back null then do not continue the module.

--

Dave Peterson

Nulls within a .Find command
 

Thanks Dave.

I changed this to more closely do what I want.

If FoundCell Is Nothing Then
Exit Sub
Else
ColToFind = FoundCell.Column
End If


However, when I run the code I get this error:

Run-time error '91':
Object variable or With block not set

When I hit debug it highlights

Set FoundCell = .Find(What:="Porter, Kevin", After:=.Cells(.Columns.Count), _
SearchOrder:=xlByRows).Column

ANy ideas why?



"Dave Peterson" wrote:

Dim FoundCell as Range
dim ColToFind as long

With Workbooks("Workbookname.xls").Sheets("Total").Rows (1)
set foundcell = .Find(What:="What I am looking for", _
After:=.Cells(.Columns.Count), SearchOrder:=xlByRows)
end with

if foundcell is nothing then
Coltofind = 99999 'what do you want?
else
coltofind = foundcell.column
end if


Kevin Porter wrote:

Here is the code:

With Workbooks("Workbookname.xls").Sheets("Total").Rows (1)

ColToFind = .Find(What:="What I am looking for",
After:=.Cells(.Columns.Count), _
SearchOrder:=xlByRows).Column

End With

The problem is sometimes ColToFind is not going to be there. I need a way
to tell it that if ColToFind comes back null then do not continue the module.

--

Dave Peterson

Nulls within a .Find command
 

Drop the .column at the end of this line:

Set FoundCell = .Find(What:="Porter, Kevin", After:=.Cells(.Columns.Count), _
SearchOrder:=xlByRows).Column

Set FoundCell = .Find(What:="Porter, Kevin", After:=.Cells(.Columns.Count), _
SearchOrder:=xlByRows)



Kevin Porter wrote:

Thanks Dave.

I changed this to more closely do what I want.

If FoundCell Is Nothing Then
Exit Sub
Else
ColToFind = FoundCell.Column
End If

However, when I run the code I get this error:

Run-time error '91':
Object variable or With block not set

When I hit debug it highlights

Set FoundCell = .Find(What:="Porter, Kevin", After:=.Cells(.Columns.Count), _
SearchOrder:=xlByRows).Column

ANy ideas why?

"Dave Peterson" wrote:

Dim FoundCell as Range
dim ColToFind as long

With Workbooks("Workbookname.xls").Sheets("Total").Rows (1)
set foundcell = .Find(What:="What I am looking for", _
After:=.Cells(.Columns.Count), SearchOrder:=xlByRows)
end with

if foundcell is nothing then
Coltofind = 99999 'what do you want?
else
coltofind = foundcell.column
end if


Kevin Porter wrote:

Here is the code:

With Workbooks("Workbookname.xls").Sheets("Total").Rows (1)

ColToFind = .Find(What:="What I am looking for",
After:=.Cells(.Columns.Count), _
SearchOrder:=xlByRows).Column

End With

The problem is sometimes ColToFind is not going to be there. I need a way
to tell it that if ColToFind comes back null then do not continue the module.

--

Dave Peterson


--

Dave Peterson

Nulls within a .Find command
 

Thank you!!!!!

"Dave Peterson" wrote:

Drop the .column at the end of this line:

Set FoundCell = .Find(What:="Porter, Kevin", After:=.Cells(.Columns.Count), _
SearchOrder:=xlByRows).Column

Set FoundCell = .Find(What:="Porter, Kevin", After:=.Cells(.Columns.Count), _
SearchOrder:=xlByRows)



Kevin Porter wrote:

Thanks Dave.

I changed this to more closely do what I want.

If FoundCell Is Nothing Then
Exit Sub
Else
ColToFind = FoundCell.Column
End If

However, when I run the code I get this error:

Run-time error '91':
Object variable or With block not set

When I hit debug it highlights

Set FoundCell = .Find(What:="Porter, Kevin", After:=.Cells(.Columns.Count), _
SearchOrder:=xlByRows).Column

ANy ideas why?

"Dave Peterson" wrote:

Dim FoundCell as Range
dim ColToFind as long

With Workbooks("Workbookname.xls").Sheets("Total").Rows (1)
set foundcell = .Find(What:="What I am looking for", _
After:=.Cells(.Columns.Count), SearchOrder:=xlByRows)
end with

if foundcell is nothing then
Coltofind = 99999 'what do you want?
else
coltofind = foundcell.column
end if


Kevin Porter wrote:

Here is the code:

With Workbooks("Workbookname.xls").Sheets("Total").Rows (1)

ColToFind = .Find(What:="What I am looking for",
After:=.Cells(.Columns.Count), _
SearchOrder:=xlByRows).Column

End With

The problem is sometimes ColToFind is not going to be there. I need a way
to tell it that if ColToFind comes back null then do not continue the module.

--

Dave Peterson


--

Dave Peterson