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


"Cutter" <Cutter.1xzo82_1131135798.4626@excelforum-nospam.com> wrote in
message news:Cutter.1xzo82_1131135798.4626@excelforum-nospam.com...
>
> 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).TopLeftCell.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
>