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.

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.

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.

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.

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.

Thanks a million everyone, very much obliged!