VBA Excel Range Problem
Posted by Mary on January 17, 2002 9:28 PM
Hi, I need help. I am trying to construct a simulation model in excel. I am having a problem referencing a changing datarange. In my model, a for loop is increasing the number of values in my chart and hence the referenced values for the chart keep changing. How do I do this????
Here is what I tried, but it won't except it.
(counter is just a normal counter that increases with each iteration of the for loop)
Source:=Worksheets("Sheet3").Range(.Cells(1, 1), .Cells(counter, 2))
Someone PLEASE help
THanks
Posted by Damon Ostrander on January 17, 2002 11:44 PM
Hi Mary,
The reason your code didn't work is that the Cells objects are improperly qualified. By putting the dot in front of them you are implying that you have them between With..End With statements. Here's how the statement should look:
With Worksheets("Sheet3")
...Source:=.Range(.Cells(1, 1), .Cells(counter, 2))
End With
Note that this qualifies ALL the ranges (a cell is a special case of a range--there is no cell object) with the worksheet.
I added the "..." in front of Source since I'm assuming that it is the Source argument of the SetSourceData method that is not shown.
I hope this helps.
Damon
Posted by Damon Ostrander on January 17, 2002 11:45 PM
Hi Mary,
The reason your code didn't work is that the Cells objects are improperly qualified. By putting the dot in front of them you are implying that you have them between With..End With statements. Here's how the statement should look:
With Worksheets("Sheet3")
...Source:=.Range(.Cells(1, 1), .Cells(counter, 2))
End With
Note that this qualifies ALL the ranges (a cell is a special case of a range--there is no cell object) with the worksheet.
I added the "..." in front of Source since I'm assuming that it is the Source argument of the SetSourceData method that is not shown.
I hope this helps.
Damon
