Assign cell values based on Range variable

1
votes

I currently have code that copies values from several ranges, and pastes those values into other ranges. The scope of this project is constantly changing, so the ranges need to be changed in my VBA code every time a row or column is added. I'm trying to streamline this by creating Global range variables to store the range locations, and have my copy/paste code reference these variables. 

Public test As Range
Public def1 As Range

Public Sub initializeGlobalVars()

'Assign values to the global variables

Set def1 = Sheets("Defaults").Range("B10:D14")

Set test = Sheets("Defaults").Range("B32:D36")
test = def1

End Sub

I know i'm missing some line here or am approaching it incorrectly, but I'd like to be able to change the values of the actual cells in the variables "test" by referring to the variable "test" rather than the cell location, since it is constantly changing. Is this possible?

Thank you for the help!

2
excelvbavariablesrange
If you don't know how those ranges change, then it is not possible to automate it. Is there any logic behind the changes? Can you provide a real example of what happened and the desired results? - jivko

2 Answers

0
votes

You can used TABLES , or NAMED RANGES

1- Tables have a name and can be used for determine some range who is dynamic! That way u can use ActiveSheet.ListObjects("table").range()

Here is the table

2- You can Create a named range

That way you can use 

Range("RAGEDNUMBERONE")

Ranged Named

0
votes

Because Range takes a string variable, you can just do something like the following:

Dim strRangeToUse as String
strRangeToUse = "B10:D14"

Set test = Sheets("Defaults").Range(strRangeToUse)

If you then want to set another range (strRangeToFill) with the test range, you can do the following:

Sheets("Defaults").Range(strRangeToFill).Value = test.Value