Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select specific cells in same row as active cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select specific cells in same row as active cell
Try this
Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "J")).Select -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "michaelberrier" wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select specific cells in same row as active cell
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try this Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "J")).Select -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "michaelberrier" wrote in message ups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select specific cells in same row as active cell
Perfect sir, thank you.
On Jan 25, 1:06 pm, "Ron de Bruin" wrote: Try this Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "J")).Select -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "michaelberrier" wrote in oglegroups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select specific cells in same row as active cell
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. On Jan 25, 1:13 pm, "Ron de Bruin" wrote: 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 -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in l... Try this Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "J")).Select -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "michaelberrier" wrote in oglegroups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row select mode to highlight active row of active cell | Excel Discussion (Misc queries) | |||
Select Active Cells | Excel Worksheet Functions | |||
how to select row according active cell? | Excel Programming | |||
how do I select cells in column A thru F in the active row? | Excel Programming | |||
Select column cells to the left/right of active selection - an example | Excel Programming |