PDA

View Full Version : Solved: How to do arithmetic on addresses



pete_l
08-11-2008, 11:12 AM
Here's the background. I have a sheet that contains a few columns (the number of columns will vary). I intend to create some charts (graphs) based on the contents of these columns.
The first column is always a time column and this will be the X-axis of each chart.
I have some code that selects a lower and upper limit from the times and stores the cell address of these limits in 2 variables, called t_start and t_end. I then create a range from these limits which is stored in another variable, thus.
t_range = t_start & ":" & t_end So if t_start contained "$A$15" and t_end contained "$A$200", then the range I've built is "$A$15:$A$200" - fine so far - I can pass this into the code that creates the chart, and that all works.

What I'm having a lot of difficuilty with is creating a range from the second column: the one that contains the Y-data for the chart. Using the example above, the variable should contain "$B$15:$B$200". All the mixtures of .offset(1,0), .address etc. don't give me the result I need - or simply won't compile.
Ideally I want to fill in the gaps for either of the following statements
y_range = <something>t_start & ":" & <something>t_end ... or:
y_range = <some function>(t_range).<some operator> + 1 column
Any help would be gratefully received.
Pete

Kenneth Hobs
08-11-2008, 11:35 AM
Once you set the first, then offset(0,1).
Sub test()
Dim aRange As Range, bRange As Range
Set aRange = Range("A15:A200")
Set bRange = aRange.Offset(0, 1)
MsgBox bRange.Address
End Sub

pete_l
08-11-2008, 01:37 PM
Yes, thanks for that Kenneth - I was missing the "Set" - works a treat now.