.Find Malfunctioning, Object Issue

**Centexcel** · 10-02-2013, 01:39 PM

CopyWorksheet.xls
PPU.xlsx.xlsm

Excel Forum,

I am having what I think are object variable issues and I would like to learn why my macros break down when I paste them from my personal workbook into their respective workbooks. Part of this macro worked in my personal workbook.
Attached are 2 workbooks. In the PPU workbook, my macro executes well until it comes across the .find function. I have tried altering my object variables and format type (adding or taking away ' before the search numbers).

Why is my .find method not working?

If you need further context:
I am trying to fill out the PPU worksheet by playing the macro, selecting each file I need, then using the macro to copy and paste the data.

Thank you for your help. This forum is invaluable and one day I hope my skills are good enough to help others.

**MarvinP** · 10-02-2013, 01:48 PM

Hi,

I opened your PPU workbook and find your code behind a worksheet instead of in a module. This raises a red flag for me in terms of SCOPE (not the mouthwash). My general rule is that code behind a worksheet only works on that worksheet. If you want to pull stuff from another sheet or workbook you should have your code in a Module that can see other sheets or workbooks.
Read http://www.ozgrid.com/VBA/variable-scope-lifetime.htm
for more info on the topic.

Scope of VBA is like creating a variable using the Dim statement in a subroutine and trying to use it in another. The variable is destroyed as soon as the sub ends, unless you declare it as global.....

If I'm way off base, keep asking and we can read the code but having your VBA behind a worksheet instead of behind a module is my first guess of the problem.

**Centexcel** · 10-02-2013, 01:57 PM

Marvin,

I see your point and yes, you are correct. I am self taught and make a lot of simple errors like this.

However, as I run the code in a module, it breaks down when I am setting my variable. For example everything looks good in my watch window until I try to set my "advert" variable. I get an object required run-time error. What troubles me is this method executes in other modules just fine and I don't know why the object would change when I am setting the variables. Any ideas?

Thank you for your time. Now I will be more mindful to place my code in modules where they belong.

**Norie** · 10-02-2013, 01:58 PM

As far as I can see you've implemented the Find properly,

It's the code following it that doesn't look right.

For example what are you doing here?

Please Login or Register  to view this content.

That first line is going to set keyRow to a Double variable, but on the next line you try to refer to it as if it's a range.

What is the first line supposed to do?

If I remove every instance of it in the code it runs without error until it reaches this section where the formulas fail as a lot of the variables are empty, some of them haven't been declared either, eg MakeRdy.

Please Login or Register  to view this content.

PS You should add Option Explicit at the top of the code, that should help weed out any undeclared variables.

**Centexcel** · 10-02-2013, 02:09 PM

Norie,

Thank you for your reply.

When I finish a loop I declare my variable again. I am self taught from a book, and the books mentions this is important to make sure the variable doesn't sum as it goes through the loop.
In the second line I am trying to identify the row my search key is on, then return the value in the 14th column of that row.

My apologies for the missing variables, while trouble shooting I cut and pasted portions of code that work from other workbooks.

I ran it again taking out keyrow=keyrow, but I got the same errors. What does your "advert =" line look like?

Thank you for your time.

**Centexcel** · 10-02-2013, 02:28 PM

I got the macro working, but only as a personal workbook module. It still turns up errors when I place the code in other modules.

I'll keep working to sort this out. Thank you for your time and patience with me.

**Norie** · 10-02-2013, 02:31 PM

What do you mean the 'line look like'?

The only thing I changed in the code was removing keyrow = keyrow, which is something you definitely shouldn't have.

Also, what loop and what summing?

As far as I can see there is no loop and there are no calculations, summing or otherwise.

By the way, why do you have this nine times?

Please Login or Register  to view this content.

KeyRange is never used in the code.

The following code works apart from the formulas which I've commented out, they don't work because the variables are empty.

Please Login or Register  to view this content.

**Centexcel** · 10-02-2013, 03:55 PM

Thank you Noire.
I will give this a shot.

Where did you receive your VBA training? I think I will need more instruction than my books.

**Norie** · 10-02-2013, 04:20 PM

I'm self taught.

.Find Malfunctioning, Object Issue

LinkBack

Thread Tools

Rate This Thread

Display

.Find Malfunctioning, Object Issue

Re: .Find Malfunctioning, Object Issue

Re: .Find Malfunctioning, Object Issue

Re: .Find Malfunctioning, Object Issue

Re: .Find Malfunctioning, Object Issue

Re: .Find Malfunctioning, Object Issue

Re: .Find Malfunctioning, Object Issue

Re: .Find Malfunctioning, Object Issue

Re: .Find Malfunctioning, Object Issue

Thread Information

Users Browsing this Thread

Similar Threads

Object Issue

Converting Column Object to Line Object Issue

Excel Help Malfunctioning?

Sorting malfunctioning

Fitter malfunctioning?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions