Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference 5 cells offset from another for copying.
Hi,
The following allows me to address the start of 5 cells I want to copy to another sheet. How do I correctly address those 5 cells, which are offset 5 places form the cell which matches a string on the other sheet, and then paste it to the sheet, 20 places horizontally from the cells which matches the string on the previous sheet. I'm using a match function to locate cells on two sheets then trying to copy 5 cells from one sheet to the other sheet but the location is different. The five cells which need to be copied are in columns "J to N" on one sheet which uses Column "E"as reference point and Columns "Z to AD" on the other sheet which also uses Column "F" . z = "" z = cell.Value 'set object from Match function Set rng3 = Sheet2.Range("E2:E100").Find(what:=z) 'find the value in sht2 Set rng5 = rng3.Offset(0, 5) '0 cells down 5 cells across Set rng4 = Sheet1.Range("F2:F65536").Find(what:=z) 'find the correct cell Set rng6 = rng4.Offset(0, 20) ' 0 cells down 5 cells across rng5.Copy Destination:=Sheet2.Range(rng6.Address) '.PasteSpecial ' copy the value Please help. -- Regards Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing worksheet reference name when copying down cells | Excel Worksheet Functions | |||
offset reference | Excel Worksheet Functions | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Code to reference offset cells - NCAA | Excel Programming | |||
Problems copying cells using offset and counta | Excel Discussion (Misc queries) |