Hello

Im trying to make a code that will help me find information in a work sheet
like a search/find tool. I have got this far but now im stuck because it
never find any infomation.

I also need to know is there any way to make to it seach in one column
instead of the whole work sheet

here's my code:
Sub BatchLocate()

Label1:
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 myRange = ActiveCell.CurrentRegion
lastRow = myRange.Rows.Count
myAnswer = InputBox("Enter Batch Number.")
myCheck = IsNumeric(myAnswer)
If myAnswer <> Empty Then
If myCheck = False Then
MsgBox " Please enter a valid batch Number"
GoTo Label1
Else
myNum = CLng(myAnswer)
End If
Else
Range("d2").Select
Exit Sub
End If

Range("d2").Value = myNum
n = 8

For k = 1 To lastRow
myRange.Cells(k).Select
If myRange.Cells(k).Value = myNum Then
Range("B" & n).Value = myNum
ActiveCell.Offset(0, -2).Copy.ActiveSheet.Cells(4, 2).Select.Paste
ActiveCell.Offset(0, 2).Copy.ActiveSheet.Cells(4, 3).Select.Paste
ActiveCell.Offset(0, 4).Copy.ActiveSheet.Cells(4, 4).Select.Paste
ActiveCell.Offset(0, 5).Copy.ActiveSheet.Cells(4, 5).Select.Paste
n = n + 1
End If
Next k
If n = 8 Then
MsgBox "Batch " & myNum & " was not found."
Else
Range("d2").Select
End If

End Sub

Any help would be great.

Thanks

the best thing to do is step through the code and wattch what is happening by
looking at the worksheet. I suspect that the active region may give you
problems bexasue it sops a blank rows and columns.

1) Go to VBA a click any where in the macro.
2) Then press F8.
3) go to worksheet and see if code is doing performing the right instructions
4) go back to vbba and press F8 again.


You can also press F9 to set a break point and use F5 to run. Look and the
differentt option in the VBA menues for more help.

"Leon" wrote:

> Hello
>
> Im trying to make a code that will help me find information in a work sheet
> like a search/find tool. I have got this far but now im stuck because it
> never find any infomation.
>
> I also need to know is there any way to make to it seach in one column
> instead of the whole work sheet
>
> here's my code:
> Sub BatchLocate()
>
> Label1:
> 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 myRange = ActiveCell.CurrentRegion
> lastRow = myRange.Rows.Count
> myAnswer = InputBox("Enter Batch Number.")
> myCheck = IsNumeric(myAnswer)
> If myAnswer <> Empty Then
> If myCheck = False Then
> MsgBox " Please enter a valid batch Number"
> GoTo Label1
> Else
> myNum = CLng(myAnswer)
> End If
> Else
> Range("d2").Select
> Exit Sub
> End If
>
> Range("d2").Value = myNum
> n = 8
>
> For k = 1 To lastRow
> myRange.Cells(k).Select
> If myRange.Cells(k).Value = myNum Then
> Range("B" & n).Value = myNum
> ActiveCell.Offset(0, -2).Copy.ActiveSheet.Cells(4, 2).Select.Paste
> ActiveCell.Offset(0, 2).Copy.ActiveSheet.Cells(4, 3).Select.Paste
> ActiveCell.Offset(0, 4).Copy.ActiveSheet.Cells(4, 4).Select.Paste
> ActiveCell.Offset(0, 5).Copy.ActiveSheet.Cells(4, 5).Select.Paste
> n = n + 1
> End If
> Next k
> If n = 8 Then
> MsgBox "Batch " & myNum & " was not found."
> Else
> Range("d2").Select
> End If
>
> End Sub
>
> Any help would be great.
>
> Thanks