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!

Pasting search results not advancing row between matches
 

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


--
Don Guillett
SalesAid Software

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

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!

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!