Find a value in a worksheet

[S Shipley]

When I run the following code I get an error on the Cells.Find line of code:

Open strFile1 For Input As #1
Open strFile2 For Output As #2
Workbooks.Open FileName:=strFile3

Do While Not EOF(1)
Line Input #1, MyRecord
MyFields = Split(MyRecord, ",")
For FieldPointer = LBound(MyFields) To UBound(MyFields)
strVar1 = MyFields(0): strVar2 = MyFields(1): strVar3 =
MyFields(2)

Range("D1").Activate
Cells.Find(What:=strVar2, After:=ActiveCell, LookIn:=xlValues,
LookAt _
:=xlWhole, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False).Activate
' Process data
Next
Loop

The error message says "Object variable or With block variable not set"
This code was working so not quite sure why it quit working.

I'm running the code in VB 6.0

Thanks,Sam

 

[Gary''s Student]

you need to do an assignment

Dim whatever as Range
Set whatever = Cells.Find(..............)

 

[S Shipley]

When you say whatever as Range are you saying set the actual range i.e.
Dim D1 as Range

I want to activate cell D1 and then do a search for my value in this column.
I know the value is in a row in column D and I need to get the address of
the row so I can read all the cells values in that row.

If I change Cells.Find(What:=strVar2,... to Cells.Find(What:=123456,... the
code works, it just doesn't work when I try to replace the actual value with
a variable.

 

[Jef Gorbach]

Close, what he's saying is
Dim whatever as Range

Open strFile1 For Input As #1
Open strFile2 For Output As #2
Workbooks.Open FileName:=strFile3
Do While Not EOF(1)
Line Input #1, MyRecord
MyFields = Split(MyRecord, ",")
For FieldPointer = LBound(MyFields) To UBound(MyFields)
strVar1 = MyFields(0): strVar2 = MyFields(1): strVar3
=MyFields(2)
Range("D1").Activate
set whatever = Cells.Find(What:=strVar2, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate
' Process data




Next
Loop

 

[Jef Gorbach]

close, what he means is alter you code to use "whatever" as a place-
holder variable for the Find results like so:

Dim whatever as Range
Open strFile1 For Input As #1
Open strFile2 For Output As #2
Workbooks.Open FileName:=strFile3
Do While Not EOF(1)
Line Input #1, MyRecord
MyFields = Split(MyRecord, ",")
For FieldPointer = LBound(MyFields) To UBound(MyFields)
strVar1 = MyFields(0)
strVar2 = MyFields(1)
strVar3 = MyFields(2)
set whatever = cells.Find(What:=strVar2, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

'Process data
'at this point the variable "whatever" contains the
'current find results so you could use whatever.row
'to access the currently found row for example

Next
Loop

 

[S Shipley]

Thanks Jef - I found the reason I was getting the error. The code executes
fine the way I had it until it tries to find a value that is not in the
worksheet then it fails. With the change you suggested it doesn't fail when
the value is not found.