Excel VBA-Change chart range to variable
 

Dim Rng as Range
Dim strName As Object
Set strName = Worksheets("12 15 03 07 KWSC1_1a").Range("O2")

How can I assign a variable to the range, rather than having the "O2
cell reference? I've seen the following:

Dim Rng as Range
Dim myRange As Object
Set myRange = Worksheets("Sheet1").Range(Cells(1,1).Cells(10,5))

...as a way to use Cells and thus vary the column & row.

How can I set the range to something like:
Range(Cells(1,1))?

Also, how can I make the following code based on variables, already th
intStartRow & intEndRow are based on integer variables. The "C" & "O
indicate columns and I want to be able vary those.

strChartRange = "C" & intStartRow & ":C" & intEndRow & ",O" & _
intStartRow & ":O" & intEndRow

Lastly, is there any way set "Sheet1" as a variable? I have a list o
worksheets and would like to be able to run this code on each workshee
in the list.

Thank

--
Message posted from http://www.ExcelForum.com

Excel VBA-Change chart range to variable
 

sSheetName = "something"
Set myRange = Worksheets(sSheetname).Cells(i,j)

----------

sSheetName = "something"
strChartRange = "C" & intStartRow & ":C" & intEndRow & ",O" & _
intStartRow & ":O" & intEndRow

set MyRange = worksheets(sSheetname).Range(strChartRange)

--
Regards,
Tom Ogilvy



"waveracerr " wrote in message
...
Dim Rng as Range
Dim strName As Object
Set strName = Worksheets("12 15 03 07 KWSC1_1a").Range("O2")

How can I assign a variable to the range, rather than having the "O2"
cell reference? I've seen the following:

Dim Rng as Range
Dim myRange As Object
Set myRange = Worksheets("Sheet1").Range(Cells(1,1).Cells(10,5))

..as a way to use Cells and thus vary the column & row.

How can I set the range to something like:
Range(Cells(1,1))?

Also, how can I make the following code based on variables, already the
intStartRow & intEndRow are based on integer variables. The "C" & "O"
indicate columns and I want to be able vary those.

strChartRange = "C" & intStartRow & ":C" & intEndRow & ",O" & _
intStartRow & ":O" & intEndRow

Lastly, is there any way set "Sheet1" as a variable? I have a list of
worksheets and would like to be able to run this code on each worksheet
in the list.

Thanks


---
Message posted from http://www.ExcelForum.com/