Hello (from a VBA amateur)

After hours of trying unsuccessfully to work this out myself I'm ready
to admit that I'm just not going to get it without help. I've searched
Google but every lead I tried resulted in an error. So please, someone,
get me back on the right track.

I am trying to assign the same macro to several different objects
located in different rows, columns and sheets. The macro is to do the
following:
--Specify a search range that is always Rows 15:5000 and in the column
that is 3 columns to the left of the objects's column.
--Go to the cell in the searched range that contains a value that
matches the value found in the cell that is 2 columns to left of object
(same row)
--Large scroll the window a number of times that is one less than the
value contained in the cell that is 3 columns to the left of the object
(same row)

Here is what I came up with:

Sub GoDownToArea()
Dim r As Range, rr As Range, rrr As Range
Dim rng As Range
Set r =
Range(ActiveSheet.Shapes(Application.Caller).TopLe ftCell.Address)
Set rr = r.Offset(0, -2)
Set rrr = r.Offset(0, -3)
On Error Resume Next
With [BQ15:BQ5000]
Application.Goto .Cells(WorksheetFunction.Match(rr.Value, .Cells, 0),
1), _Scroll:=True
If Err < 0 Then Beep: Application.Goto .Cells(1)
End With
ActiveWindow.LargeScroll Down:=rrr.Value - 1
End Sub

This works as I want it to but only because I specified the range to be
searched (BQ15:BQ5000). What I need is to replace that specific
reference with one that rng is Set to. So this is where the help is
needed. What do I Set the rng value to so that I can change the line
"With [BQ15:BQ5000]" to "With [rng].

Once that part is solved I'll be attempting to select a cell that is
offset from the active cell (that resulted from the GoTo). The offset
will be 3 columns to the right and the row offset will be determined by
a value contained in a cell on the same row as the active cell and 3
columns right of it (which is the same column as the object calling the
macro).

Thanks


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=482330

you don't mention how you come up with column BQ.

set rng = range("BQ15:BQ5000")

will set the variable, but we need some more information if this range can
be dynamic.
--


Gary



--


Gary


"Cutter" wrote in
message ...

Hello (from a VBA amateur)

After hours of trying unsuccessfully to work this out myself I'm ready
to admit that I'm just not going to get it without help. I've searched
Google but every lead I tried resulted in an error. So please, someone,
get me back on the right track.

I am trying to assign the same macro to several different objects
located in different rows, columns and sheets. The macro is to do the
following:
--Specify a search range that is always Rows 15:5000 and in the column
that is 3 columns to the left of the objects's column.
--Go to the cell in the searched range that contains a value that
matches the value found in the cell that is 2 columns to left of object
(same row)
--Large scroll the window a number of times that is one less than the
value contained in the cell that is 3 columns to the left of the object
(same row)

Here is what I came up with:

Sub GoDownToArea()
Dim r As Range, rr As Range, rrr As Range
Dim rng As Range
Set r =
Range(ActiveSheet.Shapes(Application.Caller).TopLe ftCell.Address)
Set rr = r.Offset(0, -2)
Set rrr = r.Offset(0, -3)
On Error Resume Next
With [BQ15:BQ5000]
Application.Goto .Cells(WorksheetFunction.Match(rr.Value, .Cells, 0),
1), _Scroll:=True
If Err < 0 Then Beep: Application.Goto .Cells(1)
End With
ActiveWindow.LargeScroll Down:=rrr.Value - 1
End Sub

This works as I want it to but only because I specified the range to be
searched (BQ15:BQ5000). What I need is to replace that specific
reference with one that rng is Set to. So this is where the help is
needed. What do I Set the rng value to so that I can change the line
"With [BQ15:BQ5000]" to "With [rng].

Once that part is solved I'll be attempting to select a cell that is
offset from the active cell (that resulted from the GoTo). The offset
will be 3 columns to the right and the row offset will be determined by
a value contained in a cell on the same row as the active cell and 3
columns right of it (which is the same column as the object calling the
macro).

Thanks


--
Cutter
------------------------------------------------------------------------
Cutter's Profile:
http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=482330

Hi Gary, thanks for responding. The range BQ15:BQ5000 is the range to be
searched on a particular window. It only applies to the few objects that are
in column BT. All the others will be referring to different ranges
determined by means specified in my original post
--
Happily Retired


"Gary Keramidas" wrote:

you don't mention how you come up with column BQ.

set rng = range("BQ15:BQ5000")

will set the variable, but we need some more information if this range can
be dynamic.
--


Gary



--


Gary


"Cutter" wrote in
message ...

Hello (from a VBA amateur)

After hours of trying unsuccessfully to work this out myself I'm ready
to admit that I'm just not going to get it without help. I've searched
Google but every lead I tried resulted in an error. So please, someone,
get me back on the right track.

I am trying to assign the same macro to several different objects
located in different rows, columns and sheets. The macro is to do the
following:
--Specify a search range that is always Rows 15:5000 and in the column
that is 3 columns to the left of the objects's column.
--Go to the cell in the searched range that contains a value that
matches the value found in the cell that is 2 columns to left of object
(same row)
--Large scroll the window a number of times that is one less than the
value contained in the cell that is 3 columns to the left of the object
(same row)

Here is what I came up with:

Sub GoDownToArea()
Dim r As Range, rr As Range, rrr As Range
Dim rng As Range
Set r =
Range(ActiveSheet.Shapes(Application.Caller).TopLe ftCell.Address)
Set rr = r.Offset(0, -2)
Set rrr = r.Offset(0, -3)
On Error Resume Next
With [BQ15:BQ5000]
Application.Goto .Cells(WorksheetFunction.Match(rr.Value, .Cells, 0),
1), _Scroll:=True
If Err < 0 Then Beep: Application.Goto .Cells(1)
End With
ActiveWindow.LargeScroll Down:=rrr.Value - 1
End Sub

This works as I want it to but only because I specified the range to be
searched (BQ15:BQ5000). What I need is to replace that specific
reference with one that rng is Set to. So this is where the help is
needed. What do I Set the rng value to so that I can change the line
"With [BQ15:BQ5000]" to "With [rng].

Once that part is solved I'll be attempting to select a cell that is
offset from the active cell (that resulted from the GoTo). The offset
will be 3 columns to the right and the row offset will be determined by
a value contained in a cell on the same row as the active cell and 3
columns right of it (which is the same column as the object calling the
macro).

Thanks


--
Cutter
------------------------------------------------------------------------
Cutter's Profile:
http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=482330