Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting cell below found data... (2000 SR-1)

    Good Morning All!

    I know I'm going to be completely embarassed when I hear how simple the answer is... but I don't have the time to waste trying to figure this out any more... SO I'll beg for help once again...

    I am automating a process that involves opening numerous workbooks (two at a time), copying data from one workbook to different locations in the other... I've got it all figured out except for one thing... The "Current Month" column data needs to be copied to the corresponding month column on the other sheet (There are 12 columns with April, May, etc in them) ... I am using .Find to locate the month name I need... That's fine... But when I copy the data to that location it's overwriting the month name... i.e. starting in the cell with the month... I need it to copy to that column but directly below the month name...

    Sounds simple right?... But do you think I can figure out how to add 1 row to the location I found... ???? ... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> ... Of course not!

    <img src=/S/help.gif border=0 alt=help width=23 height=15> <img src=/S/please.gif border=0 alt=please width=31 height=23>

    Here's my find line...
    Set c = YSheet.Range("G13:R13").Find(pstrResponse, LookIn:=xlValues)

    Range(c.Address).Select - takes me to the cell with "April" in it...
    If I Debug.Print c.Address I see $L$13 in the immediate window... All I want is to say... okay... now copy the data to $L$14

    TIA

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting cell below found data... (2000 SR-1)

    Thanks a million Hans... As usual I owe you big... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting cell below found data... (2000 SR-1)

    How about:

    <pre> Set c = YSheet.Range("G13:R13").Find(pstrResponse, LookIn:=xlValues).Offset(1,0)
    </pre>

    Legare Coleman

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting cell below found data... (2000 SR-1)

    You can use Offset for this. If c is a cell, c.Offset(m, n) is the cell m rows down and n columns to the right (m and n may be negative to go up and to the left, respectively).

    So in your case, c.Offset(1, 0) will take you one row down. You don't have to use Range(c.Address), since c is already a range. And you probably don't need to use Select. If you have copied something to the clipboard, c.Paste will paste the info into the worksheet, you don't have to select c for that.

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting cell below found data... (2000 SR-1)

    Perfect Legare! Thanks so much! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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