Subscript out of range

Go To StackoverFlow.com

2

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

2012-04-03 22:58
by Anicho
What line of the VBA is generating the error? SummaryRange.Range("Row:Column").Value doesn't look right to me, you should have to actually specify the Row and Column values - mellamokb 2012-04-03 23:02
Row Column is essentially, i and j, I edited it ignore SummaryRange that shouldnt be there like that. Since the change I am getting - Anicho 2012-04-03 23:11
object doesn't support this method or propert - Anicho 2012-04-03 23:12


1

Try changing:

 Sheets("Sheet1").Active

To:

 Sheets("Sheet1").Activate

The same is true for:

 Sheets("Sheet12").Active
2012-04-04 00:33
by bouvierr
As a side note, I also strongly suggest that you refer to the sheets using their object name. Instead of: Sheets("Sheet12") simply write: Sheet12 You can change the object name of a sheet by editing the "(Name)" property of the sheet in VBA Editor - bouvierr 2012-04-04 00:36
+1 for indentifying the issu - brettdj 2012-04-04 01:10


1

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
2012-04-03 23:04
by BluesRockAddict
Nadaa it should be ActiveSheet.Cells(Row, Column).Value anyway. Good spot. Although I still get same issue : - Anicho 2012-04-03 23:08
Please see my updated answer - BluesRockAddict 2012-04-03 23:33


1

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
2012-04-04 01:09
by brettdj
Thanks this taught me a bundle : - Anicho 2012-04-05 08:28
Ads