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

Topic

New Reply

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… ???? … … Of course not!

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

Reply | Quote

Replies

How about:

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

Reply | Quote

Perfect Legare! Thanks so much!

Reply | Quote

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.

Reply | Quote

Thanks a million Hans… As usual I owe you big…

Reply | Quote