ExcelBanter - An urgent modification

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - An urgent modification (https://www.excelbanter.com/excel-programming/370011-urgent-modification.html)

An urgent modification

Hi,

I’m trying to find a way to use this code:

Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
Info = ""
For Each i In RngColD
If i.Value = Range("A493").Value Then
If Info = "" Then
Info = i.Offset(, 10).Value
Else
Info = Info & "," & i.Offset(, 10).Value
End If
End If
Next i
Range("I493") = Info
Info = ""

but is taking me too long because I have to apply it cell by cell, is
there any way to modified it to work in complete columns, something
like this

Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
Info = ""
For Each i In RngColD
If i.Value = Range("A493:A519").Value Then
If Info = "" Then
Info = i.Offset(, 10).Value
Else
Info = Info & "," & i.Offset(, 10).Value
End If
End If
Next i
Range("I493:A519") = Info
Info = ""

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

An urgent modification

dim RngColD as Range, Info as Variant, Info1 as Variant
Dim s as String
s = ""
Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
Info = RndColD.Value
Info1 = RndColD.Offset(0,10).Value
For i = 1 to Ubound(info,1)
If info(i,1) = Range("A493").Value Then
If s = "" Then
s = Info1(i)
Else
s = s & "," & Info1(i)
End If
End If
Next i
Range("I493").Value = s
s = ""

--
Regards,
Tom Ogilvy

"John21" wrote:

Hi,

Im trying to find a way to use this code:

Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
Info = ""
For Each i In RngColD
If i.Value = Range("A493").Value Then
If Info = "" Then
Info = i.Offset(, 10).Value
Else
Info = Info & "," & i.Offset(, 10).Value
End If
End If
Next i
Range("I493") = s
Info = ""

but is taking me too long because I have to apply it cell by cell, is
there any way to modified it to work in complete columns, something
like this

Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
Info = ""
For Each i In RngColD
If i.Value = Range("A493:A519").Value Then
If Info = "" Then
Info = i.Offset(, 10).Value
Else
Info = Info & "," & i.Offset(, 10).Value
End If
End If
Next i
Range("I493:A519") = Info
Info = ""

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

An urgent modification

just a spelling error I would think RndColD should be RngColD

dim RngColD as Range, Info as Variant, Info1 as Variant
Dim s as String
s = ""
Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
Info = RngColD.Value
Info1 = RngColD.Offset(0,10).Value
For i = 1 to Ubound(info,1)
If info(i,1) = Range("A493").Value Then
If s = "" Then
s = Info1(i)
Else
s = s & "," & Info1(i)
End If
End If
Next i
Range("I493").Value = s
s = ""

--
Regards,
Tom Ogilvy

"John21" wrote:

dim RngColD as Range, Info as Variant, Info1 as Variant
Dim s as String
s = ""
Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
Info = RndColD.Value
Info1 = RndColD.Offset(0,10).Value
For i = 1 to Ubound(info,1)
If info(i,1) = Range("A493").Value Then
If s = "" Then
s = Info1(i)
Else
s = s & "," & Info1(i)
End If
End If
Next i
Range("I493").Value = s
s = ""

in [Info = RndColD.Value] is giving and error something about "Object
required"

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

An urgent modification

John21 Wrote:
dim RngColD as Range, Info as Variant, Info1 as Variant
Dim s as String
s = ""
Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
Info = RndColD.Value
Info1 = RndColD.Offset(0,10).Value
For i = 1 to Ubound(info,1)
If info(i,1) = Range("A493").Value Then
If s = "" Then
s = Info1(i)
Else
s = s & "," & Info1(i)
End If
End If
Next i
Range("I493").Value = s
s = ""

in [s = Info1(i)] is giving and error something about "Subscript out o
range

--
John2
-----------------------------------------------------------------------
John21's Profile: http://www.excelforum.com/member.php...fo&userid=3698
View this thread: http://www.excelforum.com/showthread.php?threadid=57042

An urgent modification

Yep, here is the correction

dim RngColD as Range, Info as Variant, Info1 as Variant
Dim s as String
s = ""
Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
Info = RngColD.Value
Info1 = RngColD.Offset(0,10).Value
For i = 1 to Ubound(info,1)
If info(i,1) = Range("A493").Value Then
If s = "" Then
s = Info1(i,1)
Else
s = s & "," & Info1(i,1)
End If
End If
Next i
Range("I493").Value = s
s = ""

--
Regards,
Tom Ogilvy

"John21" wrote:

John21 Wrote:
dim RngColD as Range, Info as Variant, Info1 as Variant
Dim s as String
s = ""
Set RngColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
Info = RndColD.Value
Info1 = RndColD.Offset(0,10).Value
For i = 1 to Ubound(info,1)
If info(i,1) = Range("A493").Value Then
If s = "" Then
s = Info1(i)
Else
s = s & "," & Info1(i)
End If
End If
Next i
Range("I493").Value = s
s = ""

in [s = Info1(i)] is giving and error something about "Subscript out of
range"

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