Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 = "" in [Info = RndColD.Value] is giving and error something about "Objec required -- John2 ----------------------------------------------------------------------- John21's Profile: http://www.excelforum.com/member.php...fo&userid=3698 View this thread: http://www.excelforum.com/showthread.php?threadid=57042 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IME MODE FOR EXCEL 2007 (URGENT URGENT) | Excel Discussion (Misc queries) | |||
last modification | Excel Discussion (Misc queries) | |||
Need exit Sub modification | Excel Programming | |||
Modification | Excel Programming | |||
Is Solver Modification Possible? PLEASE HELP! | Excel Programming |