How to specify a Range which includes an integer variable
 

The following code works, but it is probably 3 times as long as it needs to
be:

Sheets("Printed").Range("O8:O27,I4:I5").ClearConte nts
Select Case gintRoundN
Case 1: Sheets("Votes").Range("E21:K30").ClearContents
Case 2: Sheets("Votes").Range("F21:K30").ClearContents
Case 3: Sheets("Votes").Range("G21:K30").ClearContents
Case 4: Sheets("Votes").Range("H21:K30").ClearContents
Case 5: Sheets("Votes").Range("I21:K30").ClearContents
Case 6: Sheets("Votes").Range("J21:K30").ClearContents
Case 7: Sheets("Votes").Range("K21:K30").ClearContents
End Select

What I really want is to find a way of inserting a letter as the first
character of the Range ("*21:K30) with E corresponding to 1, F to 2 etc.
Can anyone put me out of my misery?

How to specify a Range which includes an integer variable
 

How about something like this:

Sheets("Votes").Range(Chr$(gintRoundN + 68) & "21:K30").ClearContents

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Hotbird" wrote in message
...
The following code works, but it is probably 3 times as long as it needs
to
be:

Sheets("Printed").Range("O8:O27,I4:I5").ClearConte nts
Select Case gintRoundN
Case 1: Sheets("Votes").Range("E21:K30").ClearContents
Case 2: Sheets("Votes").Range("F21:K30").ClearContents
Case 3: Sheets("Votes").Range("G21:K30").ClearContents
Case 4: Sheets("Votes").Range("H21:K30").ClearContents
Case 5: Sheets("Votes").Range("I21:K30").ClearContents
Case 6: Sheets("Votes").Range("J21:K30").ClearContents
Case 7: Sheets("Votes").Range("K21:K30").ClearContents
End Select

What I really want is to find a way of inserting a letter as the first
character of the Range ("*21:K30) with E corresponding to 1, F to 2 etc.
Can anyone put me out of my misery?

How to specify a Range which includes an integer variable
 

Hi
try something like

col_index = 1
Case 1:
Sheets("Votes").Range(cells(21,col_index+4),cells( 30,11)).ClearContents


--
Regards
Frank Kabel
Frankfurt, Germany


Hotbird wrote:
The following code works, but it is probably 3 times as long as it
needs to be:

Sheets("Printed").Range("O8:O27,I4:I5").ClearConte nts
Select Case gintRoundN
Case 1: Sheets("Votes").Range("E21:K30").ClearContents
Case 2: Sheets("Votes").Range("F21:K30").ClearContents
Case 3: Sheets("Votes").Range("G21:K30").ClearContents
Case 4: Sheets("Votes").Range("H21:K30").ClearContents
Case 5: Sheets("Votes").Range("I21:K30").ClearContents
Case 6: Sheets("Votes").Range("J21:K30").ClearContents
Case 7: Sheets("Votes").Range("K21:K30").ClearContents
End Select

What I really want is to find a way of inserting a letter as the
first
character of the Range ("*21:K30) with E corresponding to 1, F to 2
etc. Can anyone put me out of my misery?

How to specify a Range which includes an integer variable
 

Many thanks for this elegant solution

"Rob Bovey" wrote in message
...
How about something like this:

Sheets("Votes").Range(Chr$(gintRoundN + 68) & "21:K30").ClearContents

--

How to specify a Range which includes an integer variable
 

Hello Rob,

Could you please explain the syntax you suggest he (Chr$(gintRound
+ 68) & "21:K30")

In particular ... what is "Chr$"?

Thank

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