+ Reply to Thread
Results 1 to 10 of 10

Macro goto next blank cell

  1. 10-19-2009, 03:19 PM #1
    staples
    staples is offline
    Registered User
    Join Date
    11-22-2008
    Location
    house
    Posts
    68

    Macro goto next blank cell

    Hi there

    I have a spreadsheet where I had a new column on a daily basis. I am trying to create a macro to automate some parts of this. I would like to select the next blank cell on row 1. So in this instance, cell M1, however the following day it would be N1. This is the part I am unsure of how to do. After this I will be doing a vlookup to add the new values, which I should be able to sort out no problem.

    I have seen people using COUNT and OFFSET to select the last cell +1, however I am unsure how to link to two together.

    Any help would be great.
    Last edited by staples; 10-19-2009 at 05:06 PM.
    Reply With QuoteRegister To Reply
  2. 10-19-2009, 03:21 PM #2
    mrice
    mrice is offline
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Macro goto next blank cell

    Try

    Please Login or Register  to view this content.
    to get the next available cell in the first row. This works even if there are blanks elsewhere in the row.
    Martin
    Reply With QuoteRegister To Reply
  3. 10-19-2009, 03:41 PM #3
    staples
    staples is offline
    Registered User
    Join Date
    11-22-2008
    Location
    house
    Posts
    68

    Re: Macro goto next blank cell

    hi there

    thanks for the reply however i have tried the code and it comes up with a "compile error".... I am unsure of how to rectify this.
    Reply With QuoteRegister To Reply
  4. 10-19-2009, 03:54 PM #4
    mrice
    mrice is offline
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Macro goto next blank cell

    You need to include this as part of a larger line.
    e.g.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 10-19-2009, 04:08 PM #5
    staples
    staples is offline
    Registered User
    Join Date
    11-22-2008
    Location
    house
    Posts
    68

    Re: Macro goto next blank cell

    Hi again

    I've tried that however when I run the macro it goes to cell B256 and I have no idea why? I have attached it below is this will help. Sorry for my complete lack of knowledge.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 10-19-2009, 04:14 PM #6
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Macro goto next blank cell

    Its because you have columns.count in the row argument of the cells() property.

    Try this:

    Please Login or Register  to view this content.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave
    Reply With QuoteRegister To Reply
  7. 10-19-2009, 04:20 PM #7
    staples
    staples is offline
    Registered User
    Join Date
    11-22-2008
    Location
    house
    Posts
    68

    Re: Macro goto next blank cell

    Thanks everyone, that's working great now.... no doubt I'll be back at some other point to ask something else!
    Reply With QuoteRegister To Reply
  8. 10-19-2009, 04:56 PM #8
    mrice
    mrice is offline
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Macro goto next blank cell

    Dave

    Well spotted.
    Reply With QuoteRegister To Reply
  9. 10-19-2009, 05:33 PM #9
    staples
    staples is offline
    Registered User
    Join Date
    11-22-2008
    Location
    house
    Posts
    68

    Re: Macro goto next blank cell

    Hi everyone,

    As predicted I am back, sorry

    Although that is now working I am having a problem with the next step, which would be to create perform a vlookup, which again is on a daily basis. I will paste the new data into Sheet2 then was hoping that the vlookup could automatically run in the newly created column.

    So, the problem I am facing now how do I get the vlookup to run in the new column and not always in the same set cell. Then I also would need the vlookup to be copied down the length of the column. Again, I can only figure this out for one set column which wont "move" along each day.

    Any help would be really great
    thanks again
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 10-20-2009, 11:09 AM #10
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Macro goto next blank cell

    This is a different problem, you should start a new thread.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1