Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset issue
I'm currently writing a macro where I'm trying to copy the activated cell
plus the 10 to the right but it just keeps activating the cell 10 over to the right not the entire row. This is what I'm writing Worksheets("DATA").Activate Cells.Find(What:="Name",After:=ActiveCell,LookIn:= xlFormulas, LookAt:=xlPart, SearchOrder:=xl, SearchDirection:=xlNext, MatchCase:=False, SearchFormat: =False).Activate ActiveCell.Offset(RowOffset:=0, ColumnOffset:=10).Activate Could someone tell me why it's not working? Thanks MartÃ*n Hinojosa -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset issue
Several Options here Martin:
Range(ActiveCell,ActiveCell.Offset(,10)).Copy ActiveCell.Resize(1,11).Copy I prefer to not select cells at all as this slows stuff down: Dim R As Range Set R = Cells.Find(BlahBlahBlah) If R Is Nothing Then 'Not Found Spawn Error Message End If R.Resize(1,11).Copy This solution has the added bonus of error trapping when the search text is not found whereas Cells.Find().Activate will error out when the search does not find anything. -- Charles Chickering "A good example is twice the value of good advice." "Hinojosa via OfficeKB.com" wrote: I'm currently writing a macro where I'm trying to copy the activated cell plus the 10 to the right but it just keeps activating the cell 10 over to the right not the entire row. This is what I'm writing Worksheets("DATA").Activate Cells.Find(What:="Name",After:=ActiveCell,LookIn:= xlFormulas, LookAt:=xlPart, SearchOrder:=xl, SearchDirection:=xlNext, MatchCase:=False, SearchFormat: =False).Activate ActiveCell.Offset(RowOffset:=0, ColumnOffset:=10).Activate Could someone tell me why it's not working? Thanks MartÃ*n Hinojosa -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset issue
This is a little clumsy, but it works. For the last line use:
call range(activecell, activecell.Offset(0,10)).Select Hope this helps, dom Hinojosa via OfficeKB.com wrote: I'm currently writing a macro where I'm trying to copy the activated cell plus the 10 to the right but it just keeps activating the cell 10 over to the right not the entire row. This is what I'm writing Worksheets("DATA").Activate Cells.Find(What:="Name",After:=ActiveCell,LookIn:= xlFormulas, LookAt:=xlPart, SearchOrder:=xl, SearchDirection:=xlNext, MatchCase:=False, SearchFormat: =False).Activate ActiveCell.Offset(RowOffset:=0, ColumnOffset:=10).Activate Could someone tell me why it's not working? Thanks Martín Hinojosa -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell offset issue | Excel Worksheet Functions | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Offset issue? | Excel Programming | |||
Problem with Range.Cells.Offset and Range.Cells( row + offset, column) | Excel Programming | |||
Max Offset | Excel Discussion (Misc queries) |