CODEWITHSUNDEEP
vbaexcel
Shawn Hodgson
Shawn Hodgson

Reputation: 33

VBA Excel Show Values in MSGBOX

I am trying to create a simple Sub that grabs a range of data and displays it in a MSGBox. When I run the code I receive a Run-time error '9': Subscript out of range. Could some one assist?

Sub main()

Dim ws1 As Worksheet
Dim searchContent As Variant
Dim i As Integer
Dim txt As String

Set ws1 = ThisWorkbook.Worksheets("Sheet1")
searchContent = ws1.Range("B2:B11").Value

For i = 1 To 10
    txt = txt & searchContent(i) & vbCrLf
Next i

MsgBox (txt)

End Sub

Upvotes: 0

Views: 990

Answers (2)

Shai Rado
Shai Rado

Reputation: 33682

If you want to keep your Array as one-dimension (since you are trying to read a single column Range), you can use the Application.Transpose :

Sub main()

Dim ws1                 As Worksheet
Dim searchContent       As Variant
Dim i                   As Integer
Dim txt                 As String

Set ws1 = ThisWorkbook.Worksheets("Sheet1")

searchContent = Application.Transpose(ws1.Range("B2:B11").Value)

For i = 1 To UBound(searchContent)
    txt = txt & searchContent(i) & vbCrLf
Next i

MsgBox (txt)

End Sub

Upvotes: 1

Alex K.
Alex K.

Reputation: 175796

A Range when converted to an array is 2 dimensional so you end up with searchContent(1 to 10, 1 To 1).

To read this in your loop:

txt = txt & searchContent(i, 1) & vbCrLf

Upvotes: 1

Related Questions