MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Setting a variable macro range - tricky???

Posted by Chris C on November 23, 2001 10:39 AM

I'm trying to run a macro that runs a subroutine using a user defined range.

The entire range is January 1994 to October 2001.
The user enters a starts date (say January 1995) and an end date (say October 2000).
Spreadsheet then trims the value into two components - month ranges from 1 to 12 and date ranges from 94 to 101 (100 = yr 2000, 101 = yr 2001).

So January 1995 would return a 01 for the month and 95 for the year - October 2000 would return a 10 for the month and 100 for the year

Now, assuming that I don't allow the user to define the range (it is set for Jan 94 to Oct 2001), then my macro is defined as follows:

DIM Month, Year, Check, Check2, i as Integer
DIM Months, Years as String

i = 1
For YEAR = 94 To 101

CHECK2 = 12

If YEAR = 101 Then
CHECK2 = 10
End If



If YEAR = 100 Then
YEARS = "00"
End If

If YEAR = 101 Then
YEARS = "01"
End If

If MONTH < 10 Then
End If

Range("N" & I).Select
ActiveCell = "='" & MONTHS & "-" & YEARS & "'!R223C127]"
I = I + 1

So what this does, is from the Summary page, starting at Column N, row 1, it returns the value located at EK223 for each month from Jan 94 to Oct 01.
So if you highlighted say N6, this would correspond with June 1994 so the formula reads ='06-94'!EK$223.

So what I want to do is let the user control the range for the months (check to check2) and the years (which I guess I will have to set up new variables).

So the main question is how can I set a variable to equal a value in a cell on the worksheet?

So if the range was Jan 95 - Oct 2000, then it would create the formula in column N which references to sheets "01-95" through to "10-00".

Any help would be appreciated!

Posted by Rick E on November 23, 2001 11:51 AM

The answer to the above question is:

Pick a variable name, for this example call it data01 you can provide a dimension statement for it like: Dim data01

then this code puts a value of a cell into that variable: data01 = Range("A2").Value where cell A2 has the value you want put into data01.

Sorry to say, your code may need help! For example each variable defined in your code needs an "As" with them to define them by type, putting an "As" at the end of the Dim 'list' only makes the last item that type, so your statements:

DIM Month, Year, Check, Check2, i as Integer
DIM Months, Years as String

makes i an Integer and Years a String type the rest are all the Variant data type. Note if you don't define a variable it's type is Variant anyway.

Good Luck with your coding, note you don't need the ":" at the start of each line.

Posted by Chris C on November 23, 2001 12:57 PM

thanks rick

i'm sure my code needs lots of help i'm just kind of learning as i go along!