How do I write a select case statement for range
 

Hi,
I have a Range variable 'r'
When it is equal to certain cells I want to allocate certain values to
a 'result' varaible, for example;

When r = cell E4 result = snag
When r = cell E3 result = PQA
When r = cell E2 result = Stage 3
When r = cell E1 result = Stage 2

I guess a select case statement is my best option here.
How would I write the statement,
Thank you for your help.

How do I write a select case statement for range
 

You could try code line the following:

Select Case UCase(R.Address(False, False))
Case "A1"
Result = "is A1"
Case "B2"
Result = "is B2"
' and so on
Case Else
' address is not in a Case statement
End Select


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


"ChipButtyMan" wrote in message
...
Hi,
I have a Range variable 'r'
When it is equal to certain cells I want to allocate certain values to
a 'result' varaible, for example;

When r = cell E4 result = snag
When r = cell E3 result = PQA
When r = cell E2 result = Stage 3
When r = cell E1 result = Stage 2

I guess a select case statement is my best option here.
How would I write the statement,
Thank you for your help.

How do I write a select case statement for range
 

You don't require select case:

s = Array("snag", "PQA", "Stage 3", "Stage 2")
For i = 4 To 1 Step -1
If Not Intersect(r, Range("E" & i)) Is Nothing Then
result = s(4 - i)
End If
Next
--
Gary''s Student - gsnu200806


"ChipButtyMan" wrote:

Hi,
I have a Range variable 'r'
When it is equal to certain cells I want to allocate certain values to
a 'result' varaible, for example;

When r = cell E4 result = snag
When r = cell E3 result = PQA
When r = cell E2 result = Stage 3
When r = cell E1 result = Stage 2

I guess a select case statement is my best option here.
How would I write the statement,
Thank you for your help.

How do I write a select case statement for range
 

This single line of code (assuming the newsreader doesn't wrap it onto two
lines) will do basically the same thing as your code...

If r.Column = 5 Then result = Choose(r,Row, "Stage 2", "Stage 3", "PQA",
"snag")

except that result will be assigned Null rather than the empty string ("")
if the row number is something other than 1 through 4 (which can be tested
for using the IsNull function).

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
You don't require select case:

s = Array("snag", "PQA", "Stage 3", "Stage 2")
For i = 4 To 1 Step -1
If Not Intersect(r, Range("E" & i)) Is Nothing Then
result = s(4 - i)
End If
Next
--
Gary''s Student - gsnu200806


"ChipButtyMan" wrote:

Hi,
I have a Range variable 'r'
When it is equal to certain cells I want to allocate certain values to
a 'result' varaible, for example;

When r = cell E4 result = snag
When r = cell E3 result = PQA
When r = cell E2 result = Stage 3
When r = cell E1 result = Stage 2

I guess a select case statement is my best option here.
How would I write the statement,
Thank you for your help.

How do I write a select case statement for range
 

Fantastic! I never realized CHOOSE() was available in VBA!

Thank you also for your continued advice
--
Gary''s Student - gsnu200806


"Rick Rothstein" wrote:

This single line of code (assuming the newsreader doesn't wrap it onto two
lines) will do basically the same thing as your code...

If r.Column = 5 Then result = Choose(r,Row, "Stage 2", "Stage 3", "PQA",
"snag")

except that result will be assigned Null rather than the empty string ("")
if the row number is something other than 1 through 4 (which can be tested
for using the IsNull function).

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
You don't require select case:

s = Array("snag", "PQA", "Stage 3", "Stage 2")
For i = 4 To 1 Step -1
If Not Intersect(r, Range("E" & i)) Is Nothing Then
result = s(4 - i)
End If
Next
--
Gary''s Student - gsnu200806


"ChipButtyMan" wrote:

Hi,
I have a Range variable 'r'
When it is equal to certain cells I want to allocate certain values to
a 'result' varaible, for example;

When r = cell E4 result = snag
When r = cell E3 result = PQA
When r = cell E2 result = Stage 3
When r = cell E1 result = Stage 2

I guess a select case statement is my best option here.
How would I write the statement,
Thank you for your help.

How do I write a select case statement for range
 

Thanks a million everyone, very much obliged!