Still retuns to begining of range, Please Help

[Leon]

my the code is like a search tool I enter a batch number press
search and it looks for the batch number when it find the batch number it
then copys the the corresponding information about the batch from the
relivent cells into the the chossen cells at the top.

This is sort of what my work sheet looks like i want the info to be copyed
form the bottem then pasted in to the cells at the top. but the

clear button
searh button 581611
Requested by retrieval date Location Date to Location
peter.H N/A FOR TRAINING N/A

Peter.H 11/01/2006 587260 BASEMENT 19/01/2006
Peter.H 11/01/2006 581611 FOR TRAINING N/A N/A


so i need help with getting the ifo into the four cells

If Cells(2, 4) = R.Cells(k).Value Then
ActiveCell.Select
ActiveCell.Offset(0, -2).Copy
R2.Select
ActiveCell.PasteSpecial
R.Select

the above code works only for the first cell of the range for exsample it
will find the info i need 10 cells down paste the frist part of the info
then in will go back to the top of the range but i need it to paste all the
info from the same row.

I know it's because i have use R.select but im not sure how to fix it.

Any help would be great.

PS: Here is the full code

Sub batchlocation()

Dim R2 As Range
Set R2 = Range("C4")
Dim R3 As Range
Set R3 = Range("D4")
Dim R4 As Range
Set R4 = Range("E4")
Dim R5 As Range
Set R5 = Range("F4")

Dim myRange As Range
Dim lastRow As Integer
Dim myNum As Long
Dim myAnswer As String
Dim myCheck As Boolean
Dim n As Integer
Dim k As Integer

Range("D8").Select

Set R = ActiveCell.CurrentRegion
lastRow = R.Rows.Count
Set R = Range("A8", R(R.Count))
n = 8

For k = 1 To lastRow - 7

R.Cells(k).Select

If Cells(2, 4) = R.Cells(k).Value Then
ActiveCell.Select
ActiveCell.Offset(0, -2).Copy
R2.Select
ActiveCell.PasteSpecial
R.Select
ActiveCell.Select
ActiveCell.Offset(0, 2).Copy
R3.Select
ActiveCell.PasteSpecial
R.Select
ActiveCell.Select
ActiveCell.Offset(0, 4).Copy
R4.Select
ActiveCell.PasteSpecial
R.Select
ActiveCell.Select
ActiveCell.Offset(0, 5).Copy
R5.Select
ActiveCell.PasteSpecial
R.Select
n = n + 1
End If

Next k

If n = 8 Then
MsgBox "Batch was not found."
End If

Range("d1").Select

End Sub

 

[Tom Ogilvy]

See answer in your previous posting.

 

[Leon]

Thanks Tom it dosen't work for this worksheet but this is perfect for another
work i was working on.

is there no way of getting back to the cell from where i offset from?

 

[Tom Ogilvy]

If Cells(2, 4) = R.Cells(k).Value Then
ActiveCell.Select
set rr = ActiveCell
ActiveCell.Offset(0, -2).Copy
R2.Select
ActiveCell.PasteSpecial
R.Select
rr.select

But there is no reason the code I provided shouldn't work on this sheet
except that it might need to be fine tuned to do what you want.

 

[Leon]

Really it should work ok i'll give it a tweak and see what happens.

I just have to say that you have been a great help if it wasn't for you i
would have been total stuck.

Thanks Very much.