VBA pass string as range, type mismatch error

Question

I am trying to populate a dynamic named range into a msgBox, but getting error of type mismatch on line Set xRg = xTxt.

Below is the code:

Sub showOfferRange()
Dim xRg As Range
Dim xTxt As String
Dim xCell As Range
Dim xStr As String
Dim xRow As Long
Dim xCol As Long

On Error Resume Next
 xTxt = ThisWorkbook.Names("named_range")
Set xRg = xTxt

If xRg Is Nothing Then Exit Sub
    On Error Resume Next
    For xRow = 1 To xRg.Rows.Count
        For xCol = 1 To xRg.Columns.Count
            xStr = xStr & xRg.Cells(xRow, xCol).Value & vbTab
        Next
    xStr = xStr & vbCrLf
    Next

MsgBox xStr

End Sub

Much appreciated.

Scott Holtzman · Accepted Answer · 2017-08-02 20:43:08Z

3

You cannot set a Range object to a String variable. Hence the type mismatch.

Do this:

Set rng = Range("named_range") 'since it's a workbook level name anyway

answered Aug 2, 2017 at 20:43

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

joell Over a year ago

Awesome! Worked as expected

Scott Holtzman Over a year ago

@joell - great. consider marking as answered so others know moving forward.

Collectives™ on Stack Overflow

VBA pass string as range, type mismatch error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related