Dim cells in a sheet as a range
 

I am using a Select Case statement, and instead of having many Case
statements to cover ALL cell senarios, how can i use a line to 'DIM" an
array of cells?

Rather than something like:

Case Is = Sheet4.range("A1")
' code 1

Case Is = Sheet4.range("C3")
' code 2
Case Is = Sheet4.range("H10")
' code 3 etc............

How can i use some thing like:
Dim sh as Sheet4
Dim rng as Range
Set rng = sh.Cells("A1,C3,H10,...........")

Case Select Sheet1.range("D2").value
Case sh.rng
' code to work for ALL rng senario's....
end Select


How can i Syntax this?

Dim cells in a sheet as a range
 

Or maybe use a Defined Range in a sheet of cells instead in the Case
Statement?

"Corey" wrote in message
...
I am using a Select Case statement, and instead of having many Case
statements to cover ALL cell senarios, how can i use a line to 'DIM" an
array of cells?

Rather than something like:

Case Is = Sheet4.range("A1")
' code 1

Case Is = Sheet4.range("C3")
' code 2
Case Is = Sheet4.range("H10")
' code 3 etc............

How can i use some thing like:
Dim sh as Sheet4
Dim rng as Range
Set rng = sh.Cells("A1,C3,H10,...........")

Case Select Sheet1.range("D2").value
Case sh.rng
' code to work for ALL rng senario's....
end Select


How can i Syntax this?

Dim cells in a sheet as a range
 

Select Case UserForm1.ComboBox2
Case Is = Range("WorkShopRates") ' <= = Named range
MsgBox "W/Shop"
Case Is = Range("SiteRates") ' <= = Named range
MsgBox "Site"
End Select

Works

I think thus far anyhow


Corey....
"Corey" wrote in message
...
I am using a Select Case statement, and instead of having many Case
statements to cover ALL cell senarios, how can i use a line to 'DIM" an
array of cells?

Rather than something like:

Case Is = Sheet4.range("A1")
' code 1

Case Is = Sheet4.range("C3")
' code 2
Case Is = Sheet4.range("H10")
' code 3 etc............

How can i use some thing like:
Dim sh as Sheet4
Dim rng as Range
Set rng = sh.Cells("A1,C3,H10,...........")

Case Select Sheet1.range("D2").value
Case sh.rng
' code to work for ALL rng senario's....
end Select


How can i Syntax this?

Dim cells in a sheet as a range
 

Do you mean

Case Not Intersect(rng, Sheet1.range("D2").Value) Is NOthing

--
__________________________________
HTH

Bob

"Corey" wrote in message
...
I am using a Select Case statement, and instead of having many Case
statements to cover ALL cell senarios, how can i use a line to 'DIM" an
array of cells?

Rather than something like:

Case Is = Sheet4.range("A1")
' code 1

Case Is = Sheet4.range("C3")
' code 2
Case Is = Sheet4.range("H10")
' code 3 etc............

How can i use some thing like:
Dim sh as Sheet4
Dim rng as Range
Set rng = sh.Cells("A1,C3,H10,...........")

Case Select Sheet1.range("D2").value
Case sh.rng
' code to work for ALL rng senario's....
end Select


How can i Syntax this?

Dim cells in a sheet as a range
 

What you can do depends on how similar or different code1, code2, code3,
etc. are. Can you give us some insight into what you are doing here?

Rick


"Corey" wrote in message
...
I am using a Select Case statement, and instead of having many Case
statements to cover ALL cell senarios, how can i use a line to 'DIM" an
array of cells?

Rather than something like:

Case Is = Sheet4.range("A1")
' code 1

Case Is = Sheet4.range("C3")
' code 2
Case Is = Sheet4.range("H10")
' code 3 etc............

How can i use some thing like:
Dim sh as Sheet4
Dim rng as Range
Set rng = sh.Cells("A1,C3,H10,...........")

Case Select Sheet1.range("D2").value
Case sh.rng
' code to work for ALL rng senario's....
end Select


How can i Syntax this?