Type Mismatch when trying to join variables

Question

Any ideas how to combine the 2 'Variant' variables into a single Variant or string. The row to look for below is:

SelectColumnsRows = DisplayColumns & DisplayRows

I get the error type mismatch, because of trying to use it with ws.Range(SelectColumnsRows).Select. Any help will be great. Thanks

Sub previewSub()
   Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        Call previewDisplayColumnRow(ws)

    Next
    MsgBox "Click on each Worksheets to preview columns/rows which will be displayed after clicking on 'UPDATE LAYOUT'"
End Sub

Sub previewDisplayColumnRow(ws As Worksheet)

    Dim DisplayColumns       As Variant
    Dim DisplayRows          As Variant
    Dim HideColumnsRows      As Variant
    Dim SelectColumnsRows    As String

    Dim myrange

    Set myrange = Worksheets("Control").Range("range_sheetProperties")

    'Lookup Worksheet name and identify columns & rows to display
    HideColumnsRows = Application.VLookup(ws.Name, myrange, 5, False)
    DisplayColumns = Application.VLookup(ws.Name, myrange, 6, False)
    DisplayRows = Application.VLookup(ws.Name, myrange, 7, False)

    SelectColumnsRows = DisplayColumns & DisplayRows

   'Preview Columns / Rows which will be hidden
    If Not IsError(HideColumnsRows) Then

            If HideColumnsRows = "Y" Then
            'MsgBox ws.Name & " - " & SelectColumnsRows
            ws.Activate
            ws.Range(SelectColumnsRows).Select
            End If
    End If

End Sub

Before combining the two results of VLOOKUP check if it contains the error. Check this URL: stackoverflow.com/questions/11555728/… — Paresh J

Fumu 7 Fumu 7 · Accepted Answer · 2014-12-05T06:51:55

I guess variables DisplayColumns and DisplayRows had error-value.

Your code checks the error of 'HideColumnsRows', so you expected that error may occur. You should not use 'HideColumnsRows','DisplayColumns' and 'DisplayRows' before checking.

I modified your code for avoiding the error as follows. Try that.

Sub previewSub()
   Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        Call previewDisplayColumnRow(ws)

    Next
    MsgBox "Click on each Worksheets to preview columns/rows which will be displayed after clicking on 'UPDATE LAYOUT'"
End Sub

Sub previewDisplayColumnRow(ws As Worksheet)

    Dim DisplayColumns       As Variant
    Dim DisplayRows          As Variant
    Dim HideColumnsRows      As Variant
    Dim SelectColumnsRows    As String

    Dim myrange

    Set myrange = Worksheets("Control").Range("range_sheetProperties")

    'Lookup Worksheet name and identify columns & rows to display
    HideColumnsRows = Application.VLookup(ws.Name, myrange, 5, False)

    If Not IsError(HideColumnsRows) Then 'If no error.
        DisplayColumns = Application.VLookup(ws.Name, myrange, 6, False)
        DisplayRows = Application.VLookup(ws.Name, myrange, 7, False)

        SelectColumnsRows = DisplayColumns & DisplayRows

       'Preview Columns / Rows which will be hidden

        If HideColumnsRows = "Y" Then
            'MsgBox ws.Name & " - " & SelectColumnsRows
            ws.Activate
            ws.Range(SelectColumnsRows).Select
        End If
    Else 'Error: ws.name is not included in myrange.
        MsgBox ws.Name & " does not exist in sheet property information. Skipped.") 'Message for explain the error.
    End If

End Sub

Type Mismatch when trying to join variables

2 Answers