Results 1 to 8 of 8

Select a range based on number next to range

Threaded View

  1. #1
    Registered User
    Join Date
    01-06-2016
    Location
    Baltimore, MD
    MS-Off Ver
    Standard 2010
    Posts
    21

    Select a range based on number next to range

    Hello, I'm a novice VBA user, and have a macro that I want to tweak, because there's an operation within the macro that can take a ton of time with large files. I'm trying to copy and paste a range of cells, and I'm using a Do Until ... Loop right now.

    At this point in the macro, I have a list of SSN's in column H, starting with cell H7, and next to each one, in column I, there is a number indicating how many funds each SSN holds in their portfolio. The list has been sorted by #funds, so all the "1" fund SSN's are at the top. These are the SSN's I want to copy and paste in Column N.

    So, to recap: the list of SSN's starts in cell H7, and the list of #funds starts in cell I7. This is what I wrote:

    ActiveCell.Offset(1, -6).Range("A1").Select  [this step selects cell I7, which is always the beginning of the list in the #fund column]
        Do Until ActiveCell.Value > 1
            ActiveCell.Offset(0, -1).Range("A1").Select
            Selection.Copy
            ActiveCell.Offset(0, 6).Range("A1").Select
            ActiveSheet.Paste
            ActiveCell.Offset(1, -5).Range("A1").Select
        Loop
    So it's looking at the cell in column I, and if it's a "1", it copies the SSN next to it in column H, and pastes it into column N. Then it goes to the next cell in column I, etc. I'm copying and pasting each SSN one at a time.

    Can anyone suggest a way for the macro to select all the SSN's with a "1" next to them, and copy/paste in one grand maneuver?

    Thank you so much!!!! Jake
    Last edited by jakevogel; 07-31-2017 at 01:23 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. select range to negative column number
    By ccsmith in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-01-2016, 03:00 PM
  2. Select value based on a value being in a range
    By Nerdio in forum Excel General
    Replies: 3
    Last Post: 06-23-2014, 12:37 PM
  3. send to range, popup box to input what the range should be each time/select range
    By lax2734 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-26-2012, 01:37 AM
  4. [SOLVED] how to select a range and then add a specific number of cells after it
    By dwassner in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-03-2012, 08:41 AM
  5. select number range and copy to new tab?
    By papagorgio in forum Excel General
    Replies: 5
    Last Post: 08-26-2009, 11:08 AM
  6. Color Range Based On Number Range
    By ajocius in forum Excel General
    Replies: 1
    Last Post: 11-24-2006, 12:49 AM
  7. Select same range from large number of workbooks
    By BillC in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-18-2005, 05:06 PM

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