Returning to first offset point.
 

I Need some help please

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

Returning to first offset point.
 

I am not sure what your code is doing, so I will just give you basic
principles

Use a look

For k = 1 To LastRow - 7
If Cells(2, 4) = R.Cells(k).Value Then
R.Cells(k,-2).copy
R.Cells(k).PasteSpecial
End If
Next k

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Leon" wrote in message
...
I Need some help please

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

Returning to first offset point.
 

Sorry Bob i should of explaind it in more detail the code is like a search
tool it looks for a batch number then then it paste the the corresponding
from the relivent cells in the the chossen cells.

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

This is sort of what my work sheet looks like i enter a batch number press
search and i want the info to be pasted in to the cells at the top.

don't know if that makes any thing more clear (Hope it does)

"Bob Phillips" wrote:

I am not sure what your code is doing, so I will just give you basic
principles

Use a look

For k = 1 To LastRow - 7
If Cells(2, 4) = R.Cells(k).Value Then
R.Cells(k,-2).copy
R.Cells(k).PasteSpecial
End If
Next k

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Leon" wrote in message
...
I Need some help please

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

Returning to first offset point.
 

Sorry forgot to say thank you for your help
:)

"Bob Phillips" wrote:

I am not sure what your code is doing, so I will just give you basic
principles

Use a look

For k = 1 To LastRow - 7
If Cells(2, 4) = R.Cells(k).Value Then
R.Cells(k,-2).copy
R.Cells(k).PasteSpecial
End If
Next k

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Leon" wrote in message
...
I Need some help please

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

Returning to first offset point.
 

Try it this way:

Sub GetData()
Dim rng as Range
Dim lastRow as Long
Dim R as Range
Set R = Range("D8").CurrentRegion
lastRow = R.Rows.Count
Set R = Range("A8", R(R.Count))
Set rng = R.Find(What:=Cells(2,4), _
After:=R(R.count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if rng is nothing then
msgbox "Not found"
else
r.offset(0,-1).Resize(1,5).copy Range("C4")
End if
End Sub

--
Regards,
Tom Ogilvy


"Leon" wrote:

I Need some help please

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