Hello

I have finally work out how to make search cell to find the infomation i
need form a spreedsheet but i have a problem. when i run the programe it
starts the search from cell A1 but my the infomation starts in cell A8 and
thats were i need the search to start

i have put the code below any help would be great.

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

n = 8

For k = 1 To lastRow
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

change


Set R = ActiveCell.CurrentRegion

lastRow = R.Rows.Count
n = 8
For k = 1 To lastRow


to

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

--
Regards,
Tom Ogilvy

"Leon" wrote:

Hello

I have finally work out how to make search cell to find the infomation i
need form a spreedsheet but i have a problem. when i run the programe it
starts the search from cell A1 but my the infomation starts in cell A8 and
thats were i need the search to start

i have put the code below any help would be great.

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

n = 8

For k = 1 To lastRow
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

it works great thank you very much but can i ask one more question,

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 then in will go
back to the top of the worksheet when i need to past all the info from the
same line i know it's because i have use R.select but im not sure where to go
from there.

"Tom Ogilvy" wrote:

change


Set R = ActiveCell.CurrentRegion

lastRow = R.Rows.Count
n = 8
For k = 1 To lastRow


to

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

--
Regards,
Tom Ogilvy

"Leon" wrote:

Hello

I have finally work out how to make search cell to find the infomation i
need form a spreedsheet but i have a problem. when i run the programe it
starts the search from cell A1 but my the infomation starts in cell A8 and
thats were i need the search to start

i have put the code below any help would be great.

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

n = 8

For k = 1 To lastRow
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