Hi, I'm more or less a VBA newbie, I mainly record macros and try to adapt them. I'm not having any luck with something which I would imagine would be quite easy to do. If someone could help me with I would be very grateful, I could use this in many different ways.
The basic idea is being able to define, in VBA, a range, if you know a starting point and a variable # of rows and columns to select from the starting point. These variable numbers would be stated in named cells on a sheet.
I can easily use the OFFSET excel sheet function to do something sort of similar. If I want, for example, to sum all values in a range whose dimensions I specify on the Excel sheet as variables (# of rows, # of columns etc). So I know Excel understands instructions like "Start from here, move down 3 rows, then right by 4 columns."
What I'd like the macro to do is simply select a range based on similar instructions, when the arguments could vary.
Lets say I have three named cells:
-- "Home" contains nothing, it's just a landmark (locational reference). It could be anywhere on the sheet (though in this example it's in G16.)
-- "Move_this_many_columns_from_Home" would be validated to accept only integers.
-- "Move_this_many_rows_from_Home" also would be validated to accept only integer.
Here's what I'd like th macro to do. It would
1) go to "Home", start the selection there,
2) then continue the selection by moving down by the number of rows stated in the "Move_this_many_rows_from_Home" cell if that number is positive (if it's negative, it would move up instead of down)
3) then continue the selection by moving to the right by the number of columns stated in the "Move_this_many_columns_from_Home" cell, if that number is positive (if it's negative, it would move to the left instead of to the right)
So if Home is G16,
"Move_this_many_columns_from_Home" =1, and
"Move_this_many_rows_from_Home" =5,
the macro should select cells G16:H21.
If I then changed "Move_this_many_columns_from_Home" to 4, the macro should select G16:K21.
Etc.
I tried to record and then edit a macro, but got stuck when trying to edit. When recording, I used GoTo to get to the "Home" cell -- which is G16 in the attached example -- and then manually moved one column to the right and 5 columns down, resulting in a selection of G16:H21.
I saw that the "6" in "B6" was my "Move_this_many_rows_from_Home" value of 5, plus 1.
I have no idea how to use actual VBA syntax for this, but the following "pseudo-VBA" gives the idea of what I'd like to do:
but I couldn't figure it out.
Could someone please explain to me how to do this? This would open up a huge number of possibilities for me. I've looked a lot on the web but not found anything which addresses this narrow procedure directly in a way I can undertand.
Many thanks, and kind regards...
The basic idea is being able to define, in VBA, a range, if you know a starting point and a variable # of rows and columns to select from the starting point. These variable numbers would be stated in named cells on a sheet.
I can easily use the OFFSET excel sheet function to do something sort of similar. If I want, for example, to sum all values in a range whose dimensions I specify on the Excel sheet as variables (# of rows, # of columns etc). So I know Excel understands instructions like "Start from here, move down 3 rows, then right by 4 columns."
What I'd like the macro to do is simply select a range based on similar instructions, when the arguments could vary.
Lets say I have three named cells:
-- "Home" contains nothing, it's just a landmark (locational reference). It could be anywhere on the sheet (though in this example it's in G16.)
-- "Move_this_many_columns_from_Home" would be validated to accept only integers.
-- "Move_this_many_rows_from_Home" also would be validated to accept only integer.
Here's what I'd like th macro to do. It would
1) go to "Home", start the selection there,
2) then continue the selection by moving down by the number of rows stated in the "Move_this_many_rows_from_Home" cell if that number is positive (if it's negative, it would move up instead of down)
3) then continue the selection by moving to the right by the number of columns stated in the "Move_this_many_columns_from_Home" cell, if that number is positive (if it's negative, it would move to the left instead of to the right)
So if Home is G16,
"Move_this_many_columns_from_Home" =1, and
"Move_this_many_rows_from_Home" =5,
the macro should select cells G16:H21.
If I then changed "Move_this_many_columns_from_Home" to 4, the macro should select G16:K21.
Etc.
I tried to record and then edit a macro, but got stuck when trying to edit. When recording, I used GoTo to get to the "Home" cell -- which is G16 in the attached example -- and then manually moved one column to the right and 5 columns down, resulting in a selection of G16:H21.
Code:
Sub Select_range_from_cell_values_01()
Application.Goto Reference:="Home"
ActiveCell.Range("A1:B6").Select
End Sub
I saw that the "6" in "B6" was my "Move_this_many_rows_from_Home" value of 5, plus 1.
I have no idea how to use actual VBA syntax for this, but the following "pseudo-VBA" gives the idea of what I'd like to do:
Code:
Sub Select_range_from_cell_values_01()
Application.Goto Reference:="Home"
ActiveCell.Range("A[the value in range_ "Move_this_many_columns_from_Home"]:B[the value in range "Move_this_many_columns_from_Home" + 1 ]).Select
End Sub
but I couldn't figure it out.
Could someone please explain to me how to do this? This would open up a huge number of possibilities for me. I've looked a lot on the web but not found anything which addresses this narrow procedure directly in a way I can undertand.
Many thanks, and kind regards...