Consulting

Results 1 to 7 of 7

Thread: VBA - Select Case Error

  1. #1
    Banned VBAX Contributor
    Joined
    Aug 2017
    Posts
    144
    Location

    VBA - Select Case Error

    HTML Code:
    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
    Attached Files Attached Files

  2. #2
    Banned VBAX Contributor
    Joined
    Aug 2017
    Posts
    144
    Location
    HTML Code:
    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

  3. #3
    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

  4. #4
    Banned VBAX Contributor
    Joined
    Aug 2017
    Posts
    144
    Location
    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

  5. #5
    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

  6. #6
    Banned VBAX Contributor
    Joined
    Aug 2017
    Posts
    144
    Location
    Hi Fluff.

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

    Regards,
    mg

  7. #7
    My pleasure

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •