Houston, We've Had a Problem

[John21]

I'm getting a problem with this line of code, something about global
referense or something like that.......

Set RngColD = Range("D1", Range("D" & Rows.Count).End(xlUp))

the complete code is this....


Dim RngColD As Range
Dim i As Range
Dim Info As String
Set RngColD = Range("D1", Range("D" & Rows.Count).End(xlUp))
Info = ""
For Each i In RngColD
If i.Value = Range("A517").Value Then
If Info = "" Then
Info = i.Offset(, 20).Value
Else
Info = Info & "," & i.Offset(, 20).Value
End If
End If
Next i
Range("G517") = Info
Info = ""

End Sub

 

[Jim Thomlinson]

I see nothing distinctly wrong with what you have. Try being a little more
explicit in your referencing to the sheet perhaps. Something like this...

Dim RngColD As Range
Dim i As Range
Dim Info As String
Dim wks As Worksheet

set wks = ActiveSheet
with wks
Set RngColD = .Range(.Range("D1"), .Cells(Rows.Count, "D").End(xlUp))
end with

Info = ""
For Each i In RngColD
If i.Value = Range("A517").Value Then
If Info = "" Then
Info = i.Offset(, 20).Value
Else
Info = Info & "," & i.Offset(, 20).Value
End If
End If
Next i
Range("G517") = Info
Info = ""

End Sub