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
0
Utf
2/23/2010 1:02:01 AM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
785 Views

Similar Articles

[PageSpeed] 18

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
0
Utf
2/23/2010 1:10:01 AM
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
0
Utf
2/23/2010 3:46:15 PM
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
0
Jef
2/23/2010 4:35:51 PM
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
0
Jef
2/23/2010 4:46:04 PM
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
> .
> 
0
Utf
2/24/2010 2:41:01 AM
Reply:

Similar Artilces:

Converting pounds and ounces in worksheet for a recipe
I have a homework assignment for my cooking class and am trying to enter the information in a spreadsheet to convert to different size recipes and can't remember how to set it up to convert. Like if I have one recipe that says it is for 1 quart and you need 1lb 4 oz convert to a recipe that is 3 1/4 pints what is needed for the new recipe??? On Sun, 17 Jan 2010 10:38:01 -0800, Mary Jayne <Mary Jayne@discussions.microsoft.com> wrote: >I have a homework assignment for my cooking class and am trying to enter the >information in a spreadsheet to convert to differen...

Finding characters within a text
Hi How to check, using single formula, that a text within a certain cell contains one of certain characters? For instance, how to check if there is a 'R', 'L' or 'Ps' character within cell A1 that reads 'W-RII'. Thanks in advance Hmm..not very elegant, but maybe =NOT(AND(ISERROR(FIND("R",A1)),ISERROR(FIND("L",A1)),ISERROR(FIND("P",A1)))) Returns TRUE if the text in A1 contains 'R', 'L' or 'Ps' . FIND is case-sensitive, use SEARCH if you also want to find lower-case characters. Cheers, Joerg Mochiku...

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

Sql to find record in a hierarchical chain
Hi, lets say I have a table with 2 fields: myTable Field 1 - ref Field 2 - parentRef Now in the structure there could be numerous chains until you get to the top of the hieratchy (where the ref field = parentRef field). ie ref parentRef 111 222 222 333 333 444 444 444 so in this 444 is top of the chain (there will be many other records as well that are nothing to with 444 and are part of their own hierarchy). So how do I easily relate 111 to 444. Ie how do i find the ultimate top parent for a given 'ref' field. Hope anyone can give me some pointers ...

Can I abbreviate one value in a data series?
I've got a chart where one value (8,300) greatly exceeds all the others. Is there a way to abbreviate this value so the other data points show better in the graph? Hi, One way is to break the Y axis, have a look at these examples of how to http://peltiertech.com/Excel/Charts/BrokenYAxis.html http://tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html http://www.andypope.info/charts/brokencolumn.htm Cheers Andy CMEknit wrote: > I've got a chart where one value (8,300) greatly exceeds all the others. Is > there a way to abbreviate this value so the other da...

The Sum from 1 worksheet cell to another worksheet cell
the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula qwerty: To sum the value on Sheet1, cell A10 with the cell value Sheet2, cell B20, enter =Sheet1!A10 + Sheet2!B20 (or you can enter '=' sign and click on A10, then enter the plus sign and click on B20) jeff >-----Original Message----- >the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula >. > ...

Compare entries in 2 worksheets and list what does not match
Good Day All; I have 2 Excell works sheets with approx 14000 rows each. What I would like to do is compare both lists and get a 3rd list that shows what entries do not match. Is there a simple way to do ythis in Excel Thanks All Chomp Assuming A1 should equal A1 in the other sheet... =IF(Sheet1!A1=Sheet2!A1,"",Sheet!A1&" does not match "&Sheet2!A1) Auto-Filter for non-blanks. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "The Chomp" wrote: > Good Day All; > > I have 2 ...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

Where do I find SSADM 4+ templates in Visio ?
Hi, I understand SSADM 4+ templates were available in Visio 2002. I have just moved from Smartdraw to Visio Professional 2003 and am having difficulty finding SSADM 4+ templates. Does anyone know where I can find these templates? Are they still available in the 2002 version? ...

Somehow I created a Macro in a worksheet.
I created a macro in an Excel worksheet somehow. I didn't try to, it just happened. Now everytime I open that workbook, it asks me if I want to run the macro, disable it, etc. How the hell do I get rid of the macro? It doesn't show up under tools, macros. And it apparently doesn't do anything either because I can disable it and nothing different happens. Who invented this system anyway? Thanks, V When you record a macro, a module is created to store the macro code. There are instructions here for removing the module that is causing the prompt to appear: http://www.c...

Excel ask duplicate NAMES when duplicate a worksheets
I have added a NAME called "Above" where point to the cell just above the current cell. The formula is "=INDIRECT("R[-1]C",)" In some workbook, when I duplicate a worksheets, this name will remain silent and work ok. But in some workbooks, when I first duplicate a worksheets, the same name ABOVE will be duplicate and a new local name (belongs to that new worksheet) will be created. If I further duplicate that new worksheets in to a new worksheets, the third worksheets will be warned that a dupicate NAME is existed and ask whether refer to another name or use a ne...

Return values that sum to a known value
I have a list of data and would like to know if there is a formula that would return any items from that list that sum to a known value. Have a look at this thread for something similar: http://www.microsoft.com/office/community/en-us/default.mspx?pg=7&cat=&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&fltr= Regards, Tom "lmattern" wrote: > I have a list of data and would like to know if there is a formula that would > return any items from that list that sum to a known value. ...

XL 2007: How to find out what keyboard shortcuts I've assigned to macros?
Over the years, I've recorded and written a lot of macros. (Probably time to take a week or two and go over them and delete at least half!) I assigned a keyboard shortcut to a lot of them. I seem to remember that before XL 2007, there was a keyboard organizer that I could go through and review what shortcuts I had already assigned to my macros. I can't find that in 2007, though. Is there an easy way to generate a list of all my keyboard assignments and what macros they go to? Ed I don't recall ever seeing any such keyboard organizer in 2007. Canned from a prev...

How do I import data from lotus123 & maintain formulas/worksheets
I am trying to convert several complex Lotus 123 workbooks with formulas into Excel 2003. How do I do this and maintain my formulas and the individual tabs (worksheets). hi, if the lotus file is a wks version or earlier, xl should open it and let you save it as an xl file. if the lotus file is a 123 version or higher, you can open the file in lotus and save it as an xl file. if you don't have lotus, find someone who does. "Ineluctable" wrote: > I am trying to convert several complex Lotus 123 workbooks with formulas into > Excel 2003. How do I do this and maintai...

Find and replace with bold in cells
I have a VB6 program that is executing Excel 2007, opening a worksheet, and extracting some of the cells to write data to a text file. Some of the cells contain bold text on some (not necessarily all) of the text in the cell. I would like to do a find and replace on the bold tagging to replace it with something like "<b>" at the start of it and "</b>" at the end of it. How do I set this up in VB6? Thanks! The following function will return a string including <b> and </b> tags from the text of cell R. Function BoldMarkup(R As Range) As...

Formula to find last monday (tue, wedn, thu or friday) for a given month
Hi, I need a formula to calculate the date of the last monday, tuesday, wednesday, thursday or friday of a given month. Can't seem to find the answer anywhere. example: day: wednesday (or corresponding nr) month: 3 year: 2004 Result: 31/03/04 Who can help? Thank you for reading and eventually answering my question.Back Visit http://www.cpearson.com/excel/DateTimeWS.htm#DaysInMonth -- Kind Regards, Niek Otten Microsoft MVP - Excel "Michele" <mw001@pandora.be> wrote in message news:b30b6913.0402090708.556d0faa@posting.google.com... > Hi, > I need a...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

Can you link 2 worksheets together?
Say i have one worksheet and on my second one I want to reference cells from the first one? is there a formula for that>? To create a simple link: Select a cell in the second worksheet Type an equal sign Select the first sheet Click on the cell that you want to link Press the Enter key. Alesha wrote: > Say i have one worksheet and on my second one I want to reference cells from > the first one? is there a formula for that>? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

How do I make X-values of a chart dependent on values in cells?
Greetings. I have a chart which can go from x-value 0 to x-value 200. However I'd like to be able to input min X-value into a cell, and a max X-valu into a cell, and the x-value in the chart changes to reflect that. Is it possible to do that? Thanks for any replies. K -- Message posted from http://www.ExcelForum.com Hi, There is no automatic way to do this but take a look a Tushar's AutoChart Manager for a possible solution. (http://www.tushar-mehta.com/) Cheers Andy Kashgarinn < wrote: > Greetings. > > I have a chart which can go from x-value 0 to x-value 200...

I can't find outlook express on my Mac?
I found a folder called Outlook express, but there is no actual program. I have Internet Explorer, should't I also have Outlook Express? http://www.google.com/search?num=100&hl=en&q=outlook+express+mac what version of IE is installed? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messa...

set value of a group of activex control points
Have a spreadsheet that has some 20+ activex control points (option buttons). Is there a way to group all these controls together & set their initial values the same? Trying to setup a "reset" type of operation that would clear all control points. I can do them individually via properties, but it's too time consuming. Any suggestions? ...

Storing distinct values in an array
Hello .. in the speadsheet, i have a column containing a series of numbers maybe of them repeated multiple times... i would like to store all distinct values in an array any ideas on how i could do that ... eg , if these were the numbers going down column then 1 3 5 7 5 3 5 7 5 4 3 4 5 7 8 5 3 .. store 1,3,5,7,9,4,8 in an array, in no specifi order thank you shimee -- shimee ----------------------------------------------------------------------- shimeel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1583 View this thread: http://www.excelforum.com/showt...

Populate cell with a value if another cell has a certain value
All, good morning. i have a issue, i need to populate cell E4 with a value CX/025966 when cell F4 has a text Bond Street. Is there a simple way of doin this? EXCEL 2007 Try:- =IF((F4="Bond Street"),"CX/025966","") If my comments have helped please hit Yes. Thanks. "B2ORL" wrote: > All, good morning. i have a issue, i need to populate cell E4 with a value > CX/025966 when cell F4 has a text Bond Street. > > Is there a simple way of doin this? Use IF() =IF(F4="Bond Street","CX/02...

Show zero values
When a formula returns a zero value, the zero value is not being displayed. I have reviewed the following: - conditional formatting - zero values are checked (Tools/Options/View) - stepped through my VB code (it inserts formulae based on a Worksheet Change event) - locked and hidden values unchecked with and without protection (this should have no effect when the sheet is unprotected) Are there any other circumstances that result in a zero value not being displayed? Are there any other reasons for this occurrence? Cheers John Check also normal cell formatting! there are formats that hide ...

Comparing fields in an excel worksheet
Hi, I have some fields that I need to compare and extract the equal letters and different letters. For example, the fields are like this: DKLJSAIM; PLAIQBN. I need to compare this 2 fields and extract the differences between them. Does anyone knows how can this be done? Thanks. Hi, take a look at CPearson web http://www.cpearson.com/excel/Duplicates.aspx http://www.cpearson.com/excel/ListFunctions.aspx "David" wrote: > Hi, > > I have some fields that I need to compare and extract the equal letters and > different letters. > For example...