+ Reply to Thread
Results 1 to 3 of 3

Range.Find to find column and place value in next available cell in one line

  1. #1
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Range.Find to find column and place value in next available cell in one line

    I wanted to use this to both find a column and place a value in the next available row in that column in a single step. It won't work when the next available row is row 2. Is there another way to do this in one line?

    Please Login or Register  to view this content.
    Thanks
    Dave
    Last edited by davegugg; 09-02-2011 at 10:42 AM.
    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

  2. #2
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    1,252

    Re: Range.Find to find column and place value in next available cell in one line

    Hi Dave,

    Lots of things can be written in one line - that doesn't mean that they should be. Often it makes code harder to follow and more difficult to debug. If you find that it makes your procedure longer than you would like, you can always tuck it away in another procedure and call it - particularly if you use the code multiple times.

    In this case, my opinion is that you should split it out. For example, at the moment, if the Range.Find() method doesn't find the search value then you'll get a runtime error. The settings for LookIn, LookAt and SearchOrder are saved each time you use the Range.Find() method so as a general rule of thumb, to be sure the search is correct, you should specify them all. Obviously, in this case, some of them aren't important because they won't affect this particular search, but something along these lines:
    Please Login or Register  to view this content.
    Now if the code errors because the column is full, it'd be easier to track it down (you could defensively code for that too, of course).

    Btw, since you're searching across a single row, WorksheetFunction.Match() might be slightly quicker.
    Please Login or Register  to view this content.
    Then, if you don't have spaces in your data, you could look at using xlUp rather than xlDown.
    Please Login or Register  to view this content.

    The one liner of this (less the check that Match() found something) would be:
    Please Login or Register  to view this content.
    Last edited by Colin Legg; 09-01-2011 at 06:27 PM. Reason: added match example
    Hope that helps,

    Colin

    RAD Excel Blog

  3. #3
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Range.Find to find column and place value in next available cell in one line

    Thanks Colin.
    Thank you for that final line, I really should have known that but it just didn't come to me. Thanks for the tip about App.Match too, I'm always looking to speed up performance.

    I completely understand the importance of writing robust code, but in this case, my project is of very limited scope and will not be worked on by anyone but me, so my goal while coding is to keep write fast and write short. At a later date if I wanted to expand and "professionalize" this project, I'd certainly go back and re-write some key sections.

    Thanks
    Dave

+ 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