CODEWITHSUNDEEP
excelvbaloops
Anicho
Anicho

Reputation: 2667

Subscript out of range

I am trying to see why I am getting this issue.

Private Sub test()

Dim Row As Integer

For Row = 1 To 100

   'Loop through SummaryRange and ignore blanks but get document type'
    Dim documentTypes As String

    Dim myDocument As String, Column As Integer

    Column = 2

   'First get range from Summary'
    Sheets("Sheet1").Active

    If ActiveSheet.Cells(Row, Column).Value <> "" Then documentTypes = documentTypes + "," + ActiveSheet.Cells(Row, Column).Value

    Sheets("Sheet12").Active

    ActiveSheet.Range("B17").Value = documentTypes

Next Row

End Sub

I am trying to loop through a range in a different worksheet, then getting the values and then concatenate them into a string and output that string.

Edit:

Removed SummaryRange, gets rid of out of range issue, but brings up a Object doesn't support this property or method

Upvotes: 2

Views: 1424

Answers (3)

brettdj
brettdj

Reputation: 55692

While bouvierr has answered your existing issue I note you can actually avoid the loop and do this more efficiently

This line
strOut = Join(Application.Transpose(ws.Range("B1:B100")), ",")
produces a string of all the values from B1:B100 separated by commas

As some values may be empty the resulting string may look like something like this test,1,2,3,,,3,,afaff,,,,,,,,,,,

So I have used a regular expression to clean up multiple , into a single '

Sub QuickGrab()
Dim ws As Worksheet
Dim objRegex
Dim strOut As String

Set ws = Sheets("Sheet1")
strOut = Join(Application.Transpose(ws.Range("B1:B100")), ",")

Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Pattern = ",{2,}"
    .Global = True
    strOut = .Replace(strOut, ",")
End With

MsgBox strOut
End Sub

Upvotes: 1

bouvierr
bouvierr

Reputation: 3801

Try changing: 

 Sheets("Sheet1").Active

To:

 Sheets("Sheet1").Activate

The same is true for:

 Sheets("Sheet12").Active

Upvotes: 1

BluesRockAddict
BluesRockAddict

Reputation: 15683

Try changing

SummaryRange.Range("Row:Column").Value

to 

SummaryRange.Range(Row:Column).Value

Update: try the following

Dim range As Range
Dim row As Range
Dim cell As Range

Set range = Range("B1:B100")

For Each row In range.Rows
   For Each cell in row.Cells

      'processing code
      'documentTypes = documentTypes + "," + cell.Value

      'etc...

   Next cell
Next row

Upvotes: 1

Related Questions