Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting search results not advancing row between matches
I am trying to paste search results into my 'Main' tab in an Excel
Book. I perform the search ok but every time I try to paste results, they end up all pasting in the same cell, thus overwriting one another. Here is part of mu code: If Range("B3") < "" Then With Worksheets("Movies").Range("B:B") Set c = .Find(cellval, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Sheets("Movies").Range(c.Address).Copy Set rng = Sheets("Main").Cells(Rows.Count, 2).End(xlUp)(2).Offset(0, -1) rng.PasteSpecial xlValues Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Anyone know whats going wrong here? Any help is appreciated, thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting search results not advancing row between matches
this should work
If Range("B3") < "" Then With Worksheets("Movies").Range("B:B") Set c = .Find(cellval, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Sheets("Movies").Range(c.Address).Copy ' changed this to check column 1(A)) Set rng = Sheets("Main").Cells( _ Rows.Count,1).End(xlUp)(2) rng.PasteSpecial xlValues Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With but it may be faster to do this: If Range("B3") < "" Then Set rng = Sheets("Main").Cells( _ Rows.Count,2).End(xlUp)(2).offset(0,-1) offsetptr=0 With Worksheets("Movies").Range("B:B") Set c = .Find(cellval, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Sheets("Movies").Range(c.Address).Copy ' changed rng.offset(offsetpr,0).PasteSpecial xlValues offsetptr=offsetptr+1 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With -- Hope this helps Martin Fishlock Please do not forget to rate this reply. "Jon" wrote: I am trying to paste search results into my 'Main' tab in an Excel Book. I perform the search ok but every time I try to paste results, they end up all pasting in the same cell, thus overwriting one another. Here is part of mu code: If Range("B3") < "" Then With Worksheets("Movies").Range("B:B") Set c = .Find(cellval, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Sheets("Movies").Range(c.Address).Copy Set rng = Sheets("Main").Cells(Rows.Count, 2).End(xlUp)(2).Offset(0, -1) rng.PasteSpecial xlValues Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Anyone know whats going wrong here? Any help is appreciated, thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting search results not advancing row between matches
Sub CCC()
Dim c As Range Dim rng1 As Range Dim cellVal ' cellVal = ?? cellVal = 1 If Range("B3") < "" Then With Worksheets("Movies").Range("B:B") Set c = .Find(cellVal, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do If rng1 Is Nothing Then Set rng1 = c Else Set rng1 = Union(rng1, c) End If Set c = .FindNext(c) Loop While c.Address < firstAddress If Not rng1 Is Nothing Then rng1.Copy Sheets("Main").Cells(Rows.Count, _ 2).End(xlUp)(2,0).PasteSpecial xlValues End If End If End With End If End Sub I would assume this is notional code, because you could get the same result with Dim rng as Range, cellVal cellVal = 1 set rng = Worksheets("Movies").columns(2) With Worksheets("Main") .Cells(Rows.Count, _ 2).End(xlUp)(2,0).Resize( application.Countif(rng,cellval),1) _ .Value = cellVal End with -- Regards, Tom Ogilvy "Jon" wrote in message s.com... I am trying to paste search results into my 'Main' tab in an Excel Book. I perform the search ok but every time I try to paste results, they end up all pasting in the same cell, thus overwriting one another. Here is part of mu code: If Range("B3") < "" Then With Worksheets("Movies").Range("B:B") Set c = .Find(cellval, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Sheets("Movies").Range(c.Address).Copy Set rng = Sheets("Main").Cells(Rows.Count, 2).End(xlUp)(2).Offset(0, -1) rng.PasteSpecial xlValues Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With Anyone know whats going wrong here? Any help is appreciated, thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for matches in two columns | Excel Worksheet Functions | |||
Search for matches and then append data | Excel Discussion (Misc queries) | |||
How to search a worksheet for text-value matches? | Excel Worksheet Functions | |||
I cant do a search on this forum. Everytime I search, it comes up with zero results | Excel Programming | |||
Search code won't find partial matches for P.N.s | Excel Programming |