Hi Joel,

Thanks for the code, it is nearly there.

The first macro works good.

The second one comes up with the message box before it has even gone to any
barcode. and when you press yes it does exactly what the first macro did.


This is the whole picture of what I would like to happen:
I will scan a barcode into a1 on sheet 1,
I will then press enter/use arrow keys to exit the active cell,
This will activate the macro which will;
Do a search of the entire work book and find the first exact entry (go to
the location),
a message box (Yes/No) will pop up and ask if I want to continue searching
the rest of the workbook,
if yes do the above again (find next),
if no exit the sub.
if goes through entire workbook and no match is found, have messagebox come
up with; Barcode Not Found, Record Details.

If it helps all of the barcodes are located in column E an each sheet.


I hope the above clarified it a bit,

Joel



"joel" wrote:


I'm not sure if I got all the bus out. try a few different case and see
which cases work or don't work. there are a lot of combinations of
possibilities. the code works like find and Find Next.

There are two macros, find and find Next

'need a variable outside a sub so code will continue
'after first bar code is found and give you athe ability
'to search multiple pages.
Dim StartCell As Range
'the first Address on each page
Dim StartPage As Range
Dim FirstPage As Boolean

Sub FindFirstBarCode()
'end code is cell is blank
If ActiveCell = "" Then
Exit Sub
End If
'code to start search like using Find rather than FindNext
'this is required if you have multiple bar codes with
'the same number on the same page

Set StartCell = ActiveCell
Set StartPage = StartCell
FirstPage = True
Call FindNextBarCode
End Sub

Sub FindNextBarCode()

'end code is cell is blank
If ActiveCell = "" Then
Exit Sub
End If

If StartCell Is Nothing Then
'need this if you didn't run Findfirst after opening workbook
Set StartCell = ActiveCell
Set StartPage = ActiveCell
FirstPage = True
Else
Finish = False
If StartCell.Address(external:=True) = _
ActiveCell.Address(external:=True) Then
If Sheets.Count = 1 Then
Finish = True
Else
If FirstPage = False Then
Finish = True
End If
End If
End If

If Finish = True Then
response = MsgBox(prompt:="Found All Cells. Do you want to start
Again", _
Buttons:=vbYesNo)
If response = vbNo Then
Exit Sub
Else
Set StartCell = ActiveCell
Set StartPage = ActiveCell
FirstPage = True

End If
End If
End If



Barcode = StartCell.Value
Do
'startpage.parent is current sheet
Set c = StartPage.Parent.Cells.Find(what:=Barcode, _
after:=ActiveCell, LookIn:=xlValues, lookat:=xlWhole)

NewPage = False
If c Is Nothing Then
NewPage = True
Else
If c.Address(external:=True) = _
StartPage.Address(external:=True) Then

NewPage = True
End If
End If
If NewPage = True Then
'Move to next page
If Sheets.Count 1 Then
'only do if there is more that one page in workbook
'index is page count
PageNumber = StartPage.Parent.Index
If PageNumber = Sheets.Count Then
'Move to page 1
Set StartPage = Sheets(1).Range("A1")
Else
'Move to next Page
Set StartPage = Sheets(PageNumber + 1).Range("A1")
End If
FirstPage = False
End If

If StartPage.Value = Barcode Then
Set c = StartPage
Exit Do
End If
Else
Exit Do
End If
'loop to next page
Loop While StartPage.Address(external:=True) <
StartCell.Address(external:=True)

If Not c Is Nothing Then
c.Parent.Activate
Application.Goto Reference:=c

End If

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=162180

Microsoft Office Help

.