Houston, We've Had a Problem
 

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


--
John21
------------------------------------------------------------------------
John21's Profile: http://www.excelforum.com/member.php...o&userid=36983
View this thread: http://www.excelforum.com/showthread...hreadid=569407

Houston, We've Had a Problem
 

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

--
HTH...

Jim Thomlinson


"John21" wrote:


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


--
John21
------------------------------------------------------------------------
John21's Profile: http://www.excelforum.com/member.php...o&userid=36983
View this thread: http://www.excelforum.com/showthread...hreadid=569407