searching the entire workbook after scanning a Barcode

W

WIJ

Hi,

I am tring to write some vba code (which I dont know alot about).

I am about to do a stocktake of our equipment, What I want to do is: Scan a
barcode into A1(on sheet1), press enter and have a search on the whole
workbook done (I have about 30 sheets).

clause> there are some double ups, so when it finds one I want to keep
searching (find next) until I find the one I am looking for.

I'm not sure if my instructions where very clear, but if you could help it
would be much appreciated.

WIJ
 
J

joel

You didn't say what you wanted done with the results so I put the
addresses in a message box.

Sub FindBarCode()

BarCode = Sheets("Sheet1").Range("A1")

'skip sheet 1 and located all occurances of Bar Code
For Each sht In Sheets
If sht.Name <> "Sheet1" Then
Set c = sht.Cells.Find(what:=BarCode, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
FirstAddr = c.Address
Do
Set c = sht.Cells.FindNext(after:=c)
If Not c Is Nothing Then
MsgBox ("BarCode : " & BarCode & _
" found at location : " & c.Address(external:=True))
End If
Loop While Not c Is Nothing And c.Address <> FirstAddr

End If
End If

Next sht

End Sub
 
W

WIJ

Thanks for the code. Sorry for not explaning myself properly.

I want the macro to take me to the location(select the cell) of where the
barcode is, if I think that it is a double up then I want to be able to keep
searching.

Also when I scan the barcode, I want to be able to press enter(move off the
active cell) and the search starts automatically.
 
J

joel

I'm not sure if I got all the bus out. try a few different case and se
which cases work or don't work. there are a lot of combinations o
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 star
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 Su
 
W

WIJ

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 said:
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/showthread.php?t=162180

Microsoft Office Help

.
Click to expand...
 
J

joel

There are lots of different cases. if there is a problem see if you can
narrrow down the problem. I made soem improvemements . Make sure you
include the Global Variables which get placed outside any of the
functions so the code remembers the settings each time it is run.


'GLOBAL Variables
'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
First = True
Call FindBarCode(First)
End Sub

Sub FindNextBarCode()
'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

First = False
Call FindBarCode(First)
End Sub


Sub FindBarCode(ByVal First As Boolean)

'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
If First = True Then
Set c = StartPage.Parent.Cells.Find(what:=BarCode, _
after:=ActiveCell, LookIn:=xlValues, lookat:=xlWhole)
Else
Set c = StartPage.Parent.Cells.FindNext(after:=ActiveCell)
End If
NewPage = False


If c Is Nothing Then
NewPage = True
Else
If c.Address(external:=True) = _
StartPage.Address(external:=True) Or _
c.Address(external:=True) = ActiveCell.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
 
W

WIJ

Hi joel

Thanks for the Code. it works great. it will save me a lot of time.

Cheers,

WIJ




joel said:
There are lots of different cases. if there is a problem see if you can
narrrow down the problem. I made soem improvemements . Make sure you
include the Global Variables which get placed outside any of the
functions so the code remembers the settings each time it is run.


'GLOBAL Variables
'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
First = True
Call FindBarCode(First)
End Sub

Sub FindNextBarCode()
'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

First = False
Call FindBarCode(First)
End Sub


Sub FindBarCode(ByVal First As Boolean)

'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
If First = True Then
Set c = StartPage.Parent.Cells.Find(what:=BarCode, _
after:=ActiveCell, LookIn:=xlValues, lookat:=xlWhole)
Else
Set c = StartPage.Parent.Cells.FindNext(after:=ActiveCell)
End If
NewPage = False


If c Is Nothing Then
NewPage = True
Else
If c.Address(external:=True) = _
StartPage.Address(external:=True) Or _
c.Address(external:=True) = ActiveCell.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/showthread.php?t=162180

Microsoft Office Help

.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SEARCHING A WORKBOOK ! ! 2
VBA code for pasting links -- modifying RDB code for summary workbook 11
Highlight certain text for the entire workbook. 1
Inventory & Barcode usage, how to auto search and open up entry dataafter scanning the code 4
trouble with (complicated) vlookup searching another workbook 2
Double-sided printing and printing entire workbook in one go. Macro to help ... ?? 7
Searching this newsgroup 15
Need formulas for searching for a value in a table and returning row/column name 7

Top