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

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
>
>

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
>
>