Range.Find Method Runtime Error '91' ; MS Online Help Not Helpful

**Chaunciliscious** · 04-12-2017, 04:40 PM

So I have a worksheet where, on the "Data Entry" tab one will enter in data for a specified date, and then when a button containing the macro "Uptade_Archive" is pressed it updates another tab titled "Archive." The Archive Tab has a bunch of listed dates going down Column A starting with A3 being "Jan-17" , A4 is "Feb 17" , A5 is "Mar-17" , etc. My "Data Entry" sheet has the key date in cell A14. I want my code to take the value in cell A14, copy the data that is next to it ("A14:M14"), find that same date from A14 and find the row containing that date in the "Archive" tab, then paste the data in that row. My code currently looks as follows:

Please Login or Register  to view this content.

Whenever I run through the code step-by-step, It stops at the Selection.Find() step and gives me "Run-time error '91': Object variable or With block variable not set"
I'm not sure what exactly is wrong here, n1 is dimmed as a String and is set to the value of A14.

Please help!

**davesexcel** · 04-12-2017, 05:04 PM

Hard to tell, you have the entire column A selected, could that be the issue?
Attach a sample workbook....

**Chaunciliscious** · 04-12-2017, 05:21 PM

Well so I copied most of the code from a similar workbook that I have, written by someone else. Their code selects the whole column as well, but I've tried it while selecting just one cell as well. The code I'm working on is located in the OEE file.

**davesexcel** · 04-13-2017, 10:35 AM

Maybe just loop throught the column to find the date.

Please Login or Register  to view this content.

**Chaunciliscious** · 04-13-2017, 11:48 AM

That works beautifully, thank you. One more question. I'm wanting the macro to, after it copies and pastes the desired information in the "Archive" tab, to then find the next blank date in Archive and select that as the new "B3" value in "Data Entry"

Would you recommend creating a new variable, say "b" without quotes, Dim b As Range, and then after the last For/Next loop add an additional For/Next loop with a similar If/Then statement only with the logic necessary to copy that next value and then paste it into B3?

Or, I guess what I really need is to find the next date in "Archive" that doesn't have any data next to it, then make B3 equal to that date. But that's more complicated.

**davesexcel** · 04-13-2017, 12:15 PM

Here is additional code,

Please Login or Register  to view this content.

**Chaunciliscious** · 04-13-2017, 12:22 PM

Thank you! I can get the last few details myself. I'm a chemical engineering student, so I have almost no knowledge or experience in coding, even just VBA. I appreciate your help.

Range.Find Method Runtime Error '91' ; MS Online Help Not Helpful

LinkBack

Thread Tools

Rate This Thread

Display

Range.Find Method Runtime Error '91' ; MS Online Help Not Helpful

Re: Range.Find Method Runtime Error '91' ; MS Online Help Not Helpful

Re: Range.Find Method Runtime Error '91' ; MS Online Help Not Helpful

Re: Range.Find Method Runtime Error '91' ; MS Online Help Not Helpful

Re: Range.Find Method Runtime Error '91' ; MS Online Help Not Helpful

Re: Range.Find Method Runtime Error '91' ; MS Online Help Not Helpful

Re: Range.Find Method Runtime Error '91' ; MS Online Help Not Helpful

Thread Information

Users Browsing this Thread

Similar Threads

Runtime error '1004': Paste special method of range class error.

[SOLVED] VBA Error: Runtime Error 1004: AutoFilter method of Range class failed

Runtime error '1004' - Method 'Range' of object '_worksheet' falied

[SOLVED] Runtime error PasteSpecial method of Range class failed

"Runtime error '-2147417848 (80010108)' Method 'find' of object 'Range' failed"

Runtime error .. 20028018: Method value of object range failed

[SOLVED] Object range method runtime error

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions