Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with referencing variable range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with referencing variable range
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with referencing variable range
with cells(rrr.row,15).Resize(5000-14)
-- Regards, Tom Ogilvy "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with referencing variable range
Tom, thank you very much for the assistance. I tried inserting your line as
is (although I was confused by it) and it didn't take me to the correct area. I changed it to the way that made sense to me and it worked. with cells(15,rrr.column).Resize(4986) Thanks again for pointing me in the right direction. Now on to the next (and final) part! -- Happily Retired "Tom Ogilvy" wrote: with cells(rrr.row,15).Resize(5000-14) -- Regards, Tom Ogilvy "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with referencing variable range
Yep, that was my error - got myself confused <g
-- Regards, Tom Ogilvy "Cutter" wrote in message ... Tom, thank you very much for the assistance. I tried inserting your line as is (although I was confused by it) and it didn't take me to the correct area. I changed it to the way that made sense to me and it worked. with cells(15,rrr.column).Resize(4986) Thanks again for pointing me in the right direction. Now on to the next (and final) part! -- Happily Retired "Tom Ogilvy" wrote: with cells(rrr.row,15).Resize(5000-14) -- Regards, Tom Ogilvy "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing tab based on Variable | Excel Worksheet Functions | |||
Variable Cell Referencing | Excel Worksheet Functions | |||
referencing a named range using a variable | Excel Programming | |||
referencing worksheet as a variable | Excel Programming | |||
Referencing variable | Excel Programming |