PDA

View Full Version : VBA - Select Case Error


malleshg24
07-12-2019, 10:32 PM
Hi Team
I wanted to pass dynamically array values into select Case.Instead of 'Case "Dhoni", "Sachin" , Case ar
Getting error at :=> Case arBelow are my attempted Code. Thanks in advance

Option ExplicitSub test() Dim sh1 As Worksheet Dim sh2 As Worksheet Set sh1 = ThisWorkbook.Worksheets("Sheet1") Set sh2 = ThisWorkbook.Worksheets("Mapping") Dim lr As Long lr = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row Dim ar As Variant ar = Application.WorksheetFunction.Transpose(sh2.Range("a2:a3")) Dim i As Long For i = 2 To lr Select Case sh1.Cells(i, 1).Value 'Case "Dhoni", "Sachin" Case ar 'Getting error here want to make this line Dynamic sh1.Cells(i, 1).Font.Bold = True End Select Next i
End Sub

Regards,
mg
malleshg24
07-12-2019, 10:34 PM
Hi Team
I wanted to pass dynamically array values into select Case.Instead of 'Case "Dhoni", "Sachin" , Case ar
Getting error at :=> Case arBelow are my attempted Code.

Option ExplicitSub test() Dim sh1 As Worksheet Dim sh2 As Worksheet Set sh1 = ThisWorkbook.Worksheets("Sheet1") Set sh2 = ThisWorkbook.Worksheets("Mapping") Dim lr As Long lr = sh1.Cells(sh1.Rows.Count, "A").End(xlUp).Row Dim ar As Variant ar = Application.WorksheetFunction.Transpose(sh2.Range("a2:a3")) Dim i As Long For i = 2 To lr Select Case sh1.Cells(i, 1).Value 'Case "Dhoni", "Sachin" Case ar sh1.Cells(i, 1).Font.Bold = True End Select Next i
End Sub
Fluff
07-13-2019, 05:29 AM
You cannot use Select case like that, try
For i = 2 To lr
If UBound(Filter(ar, sh1.Cells(i, 1).Value, True, vbTextCompare)) >= 0 Then
sh1.Cells(i, 1).Font.Bold = True
End If
Next i
malleshg24
07-13-2019, 11:42 AM
Hi Fluff,

Thanks a lot , it worked perfectly,
Can you please explain this line UBound(Filter(ar, finding it hard to understand what it is does. Thanks once again.
If UBound(Filter(ar, sh1.Cells(i, 1).Value, True, vbTextCompare)) >= 0 Then


Regards,
Mallesh
Fluff
07-13-2019, 11:57 AM
You're welcome & thanks for the feedback.

For more info on the Filter function, have a look here https://www.excelfunctions.net/vba-filter-function.html
malleshg24
07-13-2019, 08:01 PM
Hi Fluff.

Thanks once again for your help:thumb in this and also suggesting website.

Regards,
mg
Fluff
07-14-2019, 04:26 AM
My pleasure:)