+ Reply to Thread
Results 1 to 14 of 14

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

  1. #1
    Registered User
    Join Date
    08-08-2010
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    85

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

    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.

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

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

    pLastRow = wbDest.Sheets("QR Tracker Pivot Renewal").Range("B65536").End(xlUp).Offset(1).row
    If solved remember to mark Thread as solved

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    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.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

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

    @ JBeaucaire,

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

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

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

  6. #6
    Registered User
    Join Date
    08-08-2010
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    85

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

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

  7. #7
    Registered User
    Join Date
    08-08-2010
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    85

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

    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?!

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

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

  9. #9
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

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

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

  10. #10
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

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

    Hi, patel45,

    my answer was correct according to variable name, but incorrect according to the use you make of it
    Quote Originally Posted by TO
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' 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
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  11. #11
    Registered User
    Join Date
    08-08-2010
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    85

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

    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...

  12. #12
    Registered User
    Join Date
    08-08-2010
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    85

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

    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!

  13. #13
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    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.
    Last edited by JBeaucaire; 09-16-2012 at 06:02 PM.

  14. #14
    Registered User
    Join Date
    08-08-2010
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    85

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1