Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a value in a worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a value in a worksheet
you need to do an assignment
Dim whatever as Range Set whatever = Cells.Find(..............) -- Gary''s Student - gsnu201001 "S Shipley" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a value in a worksheet
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. "Gary''s Student" wrote: you need to do an assignment Dim whatever as Range Set whatever = Cells.Find(..............) -- Gary''s Student - gsnu201001 "S Shipley" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a value in a worksheet
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a value in a worksheet
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a value in a worksheet
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. "Jef Gorbach" wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find in another worksheet and copy to original worksheet. | Excel Programming | |||
how to find and unlink current worksheet from old worksheet | Excel Discussion (Misc queries) | |||
Find Max Value in WorkSheet | Excel Worksheet Functions | |||
Find worksheet | Excel Programming | |||
find last row on a worksheet | Excel Programming |