Object variable error stumping me: trying to find last row and pass address to cell

[trillium]

Hi

I am so frustrated. I'm sure it's something really obvious, but I can't see it and I've been at it now for a couple hours.

I need to know at which row the last row is sitting as I'm recording it on another sheet before I add new data to it.

This is the line of code, and the full part is below... what is below works fine... but then I wanted to add this 1 line... and it now doesn't work. Thoughts on what on earth I'm doing wrong?!

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[patel45]

pLastRow = wbDest.Sheets("QR Tracker Pivot Renewal").Range("B65536").End(xlUp).Offset(1).row

[JBeaucaire]

Range variables have to be SET. Then you have to indicate the same parameter type when you use that object. So:

Please Login or Register  to view this content.

You don't gain anything using the WITH/END WITH so I took it out.

[Winon]

@ JBeaucaire,

Thank you Jerry, there is still much to learn from you, and the other Experts.

[JBeaucaire]

If that takes care of your question, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

[trillium]

Hi, sorry I had to be unexpectedly away yesterday. I had tried it with .row at the end and it didn't work.

[trillium]

Hi JBeaucaire

I tried the code you suggested, but it is returning "nothing".... does it need the .row? I tried that again and it didn't work.. what on earth am I doing wrong?!

[JBeaucaire]

Patel's answer is incorrect as well. See post #3.

[patel45]

my answer was correct according to variable name, but incorrect according to the use you make of it

[HaHoBe]

Hi, patel45,

my answer was correct according to variable name, but incorrect according to the use you make of it

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' records rows of old entries on pivot tab before they are removed '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim lRow As Range 'to capture the last row on the tab before copy and pasting over
Dim LastRow As Range 'to capture address of placement on tracker tab before it's copied to BU tab
Dim strCriteria As Long 'search address on audit #

strCriteria = wbSource.Sheets("Quality Review Input Sheet").Range("C5").Value

Dim pLastRow As Range 'last row on Pivot tab

Ciao,
Holger

[trillium]

Hi there

JBeaucaire, I thank you for your help!! I'm still not sure why it is returning "nothing"... when the data goes down to row over 1000 rows...? All I'm trying to do is record which line was the LAST line of data and place this value onto the "wsSource" in cell "AT2". When similar code is used throughout the rest of the macro to find the last line, it works fine...

The code doesn't give me an error message, it just returns nothing.... and when I manually skip through the code, it shows "nothing" as the value....

so frustrated.... been working on this for a couple days now...

[trillium]

Hi Patel45

I have used the .row in other places in the macro to find that value, but it was done in conjunction with the FIND function... thanks for your help!

[JBeaucaire]

You could use the .Row method, but you would have to change the variable declaration from As Range to As Long, then edit the syntax appropriately, too.

Please Login or Register  to view this content.

To stay with the As Range method:

Please Login or Register  to view this content.

But I typically only use variables to store a value / range / object I need to use multiple times. If I only intend to use it once, I skip the 'variable' part:

Please Login or Register  to view this content.

[trillium]

Thank you so much JBeaucaire! I have definitely learned a lot!