Pasting search results not advancing row between matches

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jon - 09 Dec 2006 22:08 GMT

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 to this Message

Don Guillett - 09 Dec 2006 22:55 GMT

try it this way. BTW where is cellval?

With Worksheets("Movies").Range("B:B")

Set c = .Find(cellval, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Set rng = Sheets("Main").Cells(Rows.Count,2).End(xlUp).row+1

Sheets("Movies").Range(c.Address).Copy
rng.PasteSpecial xlValues

Set c = .FindNext(c)

Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.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,
[quoted text clipped - 22 lines]
>
> Anyone know whats going wrong here? Any help is appreciated, thanks!

Reply to this Message

Martin Fishlock - 09 Dec 2006 23:17 GMT

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

Signature

Hope this helps
Martin Fishlock
Please do not forget to rate this reply.

> 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,
[quoted text clipped - 22 lines]
>
> Anyone know whats going wrong here? Any help is appreciated, thanks!

Reply to this Message

Tom Ogilvy - 10 Dec 2006 00:12 GMT

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

Signature

Regards,
Tom Ogilvy

Reply to this Message