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
Try changing:
Sheets("Sheet1").Active
To:
Sheets("Sheet1").Activate
The same is true for:
Sheets("Sheet12").Active
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
ActiveSheet.Cells(Row, Column).Value
anyway. Good spot. Although I still get same issue : - Anicho 2012-04-03 23:08
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
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