Set Range Variable To Growing Dynamic Range

pauldaddyadams · Mar 5th 2008

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 in yellow, the code is:

Code

Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))

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.

RichardSchollar · Mar 5th 2008

Re: Vba Set Range

Hi

You don't have a fully qualified reference. Amend to:

Code

With wksPartsData
  Set rng = .Range("a1", .Range("a65536").End(xlUp))
End With

VBA Noob · Mar 5th 2008

Re: Vba Set Range

pauldaddyadams,

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

*

http://www.excelforum.com/showthread.php?t=636090

PM Dave Hawley if you agree to the rules.

Dave, I doubt that he will as he didn't follow them at excel forum either

VBA Noob

Dave Hawley · Mar 6th 2008

Re: Vba Set Range

Thanks noob, selfish people only ever think of themselves.

Set Range Variable To Growing Dynamic Range

Participate now!

Share