Combining two VBA projects – Error

[pauldaddyadams]

Hi,

I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:

Method ‘range’ of object ‘_worksheet’ failed

The code is then highlighted

Please Login or Register  to view this content.

Meaning this part is incorrect but I don’t know why? If it works on the other spreadsheet does anyone have any suggestions why it doesn’t work on this one?

Any guidance will be appreciated as its driving me crazy

To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.

[shg]

Please stop cross posting, lest your threads become ignored or deleted.

Your second range reference is not qualified.

[pauldaddyadams]

Apologies for Cross Posting, the post has now been closed,the cross post is here http://www.ozgrid.com/forum/showthread.php?t=86165

It was suggested that I use:

Please Login or Register  to view this content.

However when using this I still get an error (run time error 91). This is the Code that is highlighted as the problem:

Please Login or Register  to view this content.

The full Code is:

Please Login or Register  to view this content.

If any has any suggestions I would REALLY appreciate it. I have literally spent hours on this.

I do not necessary need a filter. I would like it to first match what is in text box 1 within the range (the range is on sheet “wksPartData” in cells A:O) and then return the results from the search into list box 1, ideally the search would only return results where column O is greater 40 in value.

[shg]

The error is descriptive: Object variable or With block variable not set. rFilter has not been set.

I do not necessary need a filter. I would like it to first match what is in text box 1 within the range (the range is on sheet “wksPartData” in cells A:O) and then return the results from the search into list box 1

You could use the Find method, but ... if you find what's in textBox1, isn't what you find going to be what you already have?

[pauldaddyadams]

Hi,

I dont mind which method I use but I dont know how to write the code as I am a complete newbie.

When it searches it will search the text box and then I need it to return all the matches from a different sheet, there could be approx 10 different matches, each with different "yields".

[pauldaddyadams]

One other thing I have noticed is that when the command button “start” is moved to another worksheet then I get the same VBA error. I think if I crack this then I may have answered my problem above. Any suggestions?

[pauldaddyadams]

I have read that a command button will only work in its original sheet. It was suggested to use a shape rather than a command button but this didnt work.

Is this true and if so is there a workaround?

[shg]

Paul, can you go back to first principles and explain, top down, what you're trying to do? An example workbook would be helpful.

[pauldaddyadams]

Hi,

I have managed to find a work around on what I am trying to achieve, its not ideal but it will work.

I have attached the spreadsheet with it working.

My main problem now is that the start button has to be kept on its orignal worksheet. If I move it to another location it no longer works.

Any thoughts?