Solved: double case statement

[sleepy_think]

hey everyone newbs back,

Simon thanks for your help from before (works) however I found out that there is another variable I need to check now...

does the select case function have a syntax for checking 2 variables?

heres what is going on: followed on from this thread


[vba]
sub test1()

Dim Rng As Range, MyCell As Range

Set Rng = Range("M3:M50")
For Each MyCell In Rng

Select Case MyCell.Value
Case "fred"
MyCell.Offset(0, -8).Value = "Entry Found"
Case "bob"
MyCell.Offset(0, -8).Value = "Entry Found1"
End Select

Next MyCell

End Sub
[/vba]
how can I add a check for a value in range(O3:O50) which is either going to be NA or AD ? I need to get bob with NA to output one thing and bob with AD to output another. I cant seem to find the info anywhere!

thanks!

[MattKlein]

Try:

[VBA]Sub test1()
Dim Rng As Range, MyCell As Range
Set Rng = Range("M3:M50")
For Each MyCell In Rng
Select Case MyCell.Value
Case "fred"
MyCell.Offset(0, -8).Value = "Entry Found"
Case "bob"
If MyCell.Offset(0, 2).Value = "NA" Then
MyCell.Offset(0, -8).Value = "Bob NA"
Else
MyCell.Offset(0, -8).Value = "Bob AD"
End If
End Select
Next MyCell
End Sub[/VBA]

[Bob Phillips]

[vba]

Sub test1()

Dim Rng As Range, MyCell As Range

Set Rng = Range("M3:M50")
For Each MyCell In Rng

With MyCell

Select Case True

Case .Value = "fred"
.Offset(0, -8).Value = "Entry Found"
Case .Value = "bob" And .Offset(0, 2).Value = "NA"
.Offset(0, -8).Value = "Entry Found bob NA"
Case .Value = "bob" And .Offset(0, 2).Value = "AD"
.Offset(0, -8).Value = "Entry Found bob AD"
End Select
End With
Next MyCell

End Sub
[/vba]

[sleepy_think]

ok I tried both, runs through, populates the freds but doesnt populate the bobs (does the same thing for both)

also doesnt give me any errors... thoughts?

[Bob Phillips]

Not without seeing the data.

[david000]

Do you need four possible cominations?

bob na
bob ad
fred na
fred ad

output = fred + na = ({1,2,3,4})

[Simon Lloyd]

Sleepy, my thoughts are: your data isn't laid out as you explained and its possible you haven't added the text exactly as shown on your sheet in vba "bob" is not the same as "Bob" or "BOB"

[sleepy_think]

hey guys, hope your weekend was good, the text is all in caps and i just double checked and it doesnt run any of the the ones that check for NA or AD.

heres the code so far:

[vba]Dim Rng As Range, MyCell As Range

Set Rng = Range("M3:M50")
For Each MyCell In Rng

With MyCell

Select Case True

Case .Value = "fred"
.Offset(0, -8).Value = "AD-freddata"

Case .Value = "fred2"
.Offset(0, -8).Value = "AD-freddata2"

' the above works fine, the below populates nothing.

Case .Value = "bob" And .Offset(0, 2).Value = "NA"
.Offset(0, -8).Value = "NA\bob"
Case .Value = "bob" And .Offset(0, 2).Value = "AD"
.Offset(0, -8).Value = "AD\bob"

etc etc etc


End Select
End With
Next MyCell[/vba]

[sleepy_think]

And .Offset(0, 2).Value = "NA"

is what is failing, is everyone sure that you can run case statements with the And syntax?

[sleepy_think]

got it! its not

Case .Value = "bob" And .Offset(0, 2).Value = "NA"
.Offset(0, -8).Value = "NA\bob"

its

Case .Value = "bob", .Offset(0, 2).Value = "NA"
.Offset(0, -8).Value = "NA\bob"

stupid "and" was killing it.

[MattKlein]

Careful sleepy, more testing is needed. You're getting a false hit.

Here, try this code to convince yourself:

[vba]
Sub CaseAnd()
Dim x As Integer, y As Integer

x = 2
y = 4
Select Case True

Case x = 2, y = 2 'the wrong way, not true but gives a hit
msgbox "False Hit"

Case x = 2 and y = 2 'no false hit
msgbox "False"

Case x = 2 and y = 4 'this is what you want!!!
msgbox "True"

Case Else
msgbox "Miss" 'catch all

End Select
End Sub[/vba]

[Simon Lloyd]

Sleepy i think its about time you posted a workbook for all these helpful people to see what you are working with and trying to achieve!

[sleepy_think]

mattkelin,

I tried to fool around with your code but it just outputted blank cells... I added the "," isntead of the "and" (which doesnt seem to do anything) and it outputs fine and correctly.

Simon, Id like to post this but I'd get fired so I wont :P I do thank everyone for there help through

[Bob Phillips]

"," is an OR condition whereas you originally said you wanted AND, which is what I gave you.

[sleepy_think]

but "and" keeps returning blanks.

[Simon Lloyd]

Sleepy, you need to create a dummy workbook with the exact layout of data, the data needn't mean anything just as long as the layout and format are the same that way all the people giving up their valuable free time here may be compelled to help you further.

[sleepy_think]

wow im a noob,

figured it all out

xld's code is right what was wrong was the offset on the NA and AD, when I chaged them to 0,1 it ran fine!

thats why im the noob :P

thanks again everyone.