Problem with select case

[vidyavallaba]

Hi,

Im trying to use if condition inside a select case.. But the problem is... the condition is not executed..
How can i achieve this? i have to consider each row and perform multiple checks on it and insert a text in the last column. Can any one help me to break this shell??

lrw = sourcesheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lrw

Select Case invalid

Case "A"
If InStr(1, Sheet2.Range("U1").Offset(i, 0).Value, "INPAT") > 0 Or InStr(1, Sheet2.Range("U1").Offset(i, 0).Value, "EXPAT") > 0 Then
If InStr(1, Sheet2.Range("AW1").Offset(i, 0).Value, "Optimus") > 0 Then

GoTo NextRecPC6
End If
End If
NextRecPC6: Sheet2.Range("Ay1").Value = "Inpat/Expat"

End Select
Next


The above code parses each row till the last row and should check the given condition and write a text to a column

 

[Teeroy]

A Couple of things:
- If there is only one case then a Select Case is overkill.
- The way you've written the code the instruction at NextRecPC6: would be executed whether or not the conditions of the IF's are met or not as it is outside the IF block.
- invalid doesn't seem to change within the loop so you're always assessing the same condition for the Select Case.

Here is a bit of a clean up of your tests.

Dim iCondition1 As Integer, iCondition2 As Integer
lrw = sourcesheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lrw
    Select Case invalid
    Case "A"
        With Sheet2
            iCondition1 = InStr(1, .Range("U1").Offset(i, 0).Value, "INPAT") + _
                InStr(1, .Range("U1").Offset(i, 0).Value, "EXPAT")
            iCondition2 = InStr(1, .Range("AW1").Offset(i, 0).Value, "Optimus")
            If iCondition1 And iCondition2 Then
                .Range("Ay1")Offset(i, 0).Value = "Inpat/Expat" 'I assume you wanted this on each row
            End If
        End With
    End Select
Next

Hope this helps.

 

[jsotola]

looks like you have it 99.9% done

there is an error in your Select Case command i think

this will probably clear it up

here is a short example of a case statement

put text comparisons before numerical

the If then statement is probably better for just a single comparison

Sub test()


    Select Case Range("A1").Value
        Case "A"
            MsgBox "it is A"
            
        Case Is >= 100
            MsgBox ">= 100"
            
        Case Is = 50
            MsgBox "it is 50"
            
        Case 12, 14, 16
            MsgBox "it is 12 or 14 or 16"
            
        Case Else
            MsgBox "it is something other"
            
    End Select


End Sub

 

[vidyavallaba]

Thank you all for the great help. I have the following code.

Select Case sourcesheet.Range("U2").Offset(i, 0).Value

Case "INPAT"
' If InStr(1, Sheet2.Range("U1").Offset(i, 0).Value, "INPAT") > 0 Or InStr(1, Sheet2.Range("U1").Offset(i, 0).Value, "EXPAT") > 0 Then
If InStr(1, Sheet2.Range("AW1").Offset(i, 0).Value, "Optimus") > 0 Then
Sheet2.Range("Ay1").Value = "Inpat/Expat"
'GoTo NextRecPC6
End If
' End If

End Select

 

[vidyavallaba]

I have one more issue

i have a column which has count of duplicate ID's. I want to check if count>1 then perform some action

Note: i have used countif fromula to count the number of existence!

If src.Range("BA1").Offset(i, 0).Value > 1 Then 'THIS LINE GIVES AN ERROR 424 object required error
If InStr(1, Sheet2.Range("AN1").Offset(i, 0).Value, "Active") > 0 Then
Sheet2.Range("BD2").Offset(i, 0).Value = "Duplicate/Active"
End If
End If


What should i do??