Select specific cells in same row as active cell

  • Thread starter Thread starter michaelberrier
  • Start date Start date
M

michaelberrier

I need the code for selecting a specific range of cells on the same row
as the active cell. The active cell will be selected with a cell.find
operation, so it will not always be in the same column. Thus, an
activecell.offset won't work.

I tried putting the range in the code, but it failed. Here is how the
code looks now:

Sub Look_Here()
Dim WhatFor
WhatFor = ActiveSheet.Cells(4, 1)

Cells.Find(What:=WhatFor, after:=ActiveCell, SearchDirection:=xlNext,
searchorder:=xlByRows, MatchCase:=False).Activate
Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, 8)).Select <---IT
IS HERE THAT I WANT TO REFER TO CELLS A:J ON THE SAME ROW AS THE
ACTIVE(found) CELL.

thanks to all
 
Oops, wrong code.

Try this

Dim rng As Range
WhatFor = ActiveSheet.Cells(4, 1)
Set rng = Cells.Find(What:=WhatFor, after:=ActiveCell, SearchDirection:=xlNext, _
searchorder:=xlByRows, MatchCase:=False)
If Not rng Is Nothing Then
Range(Cells(rng.Row, "A"), Cells(rng.Row, "J")).Select
End If
 
Ron,
The shorter code worked just fine. It was easy to adapt to my real
purpose, which was highliting the whole range with each selection.

Thanks again to both of you.
 
Back
Top