Problem with select case

vidyavallaba

New Member
Joined
Dec 17, 2013
Messages
42
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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

Well-known Member
Joined
Nov 15, 2013
Messages
524
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


Code:
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
 
Last edited:

vidyavallaba

New Member
Joined
Dec 17, 2013
Messages
42
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

New Member
Joined
Dec 17, 2013
Messages
42
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??
 

Forum statistics

Threads
1,137,300
Messages
5,680,699
Members
419,928
Latest member
dolincasting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top