Offset issue

  • Thread starter Thread starter Hinojosa via OfficeKB.com
  • Start date Start date
H

Hinojosa via OfficeKB.com

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
 
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.
 
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
 
Back
Top