Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching the entire workbook after scanning a Barcode
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching the entire workbook after scanning a Barcode
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 -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=162180 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching the entire workbook after scanning a Barcode
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. "joel" wrote: 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 -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=162180 Microsoft Office Help . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching the entire workbook after scanning a Barcode
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching the entire workbook after scanning a Barcode
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching the entire workbook after scanning a Barcode
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/sh...d.php?t=162180 Microsoft Office Help |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
searching the entire workbook after scanning a Barcode
Hi joel
Thanks for the Code. it works great. it will save me a lot of time. Cheers, WIJ "joel" wrote: 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/sh...d.php?t=162180 Microsoft Office Help . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Barcode scanning macro | Excel Programming | |||
Scanning a barcode | Excel Discussion (Misc queries) | |||
does excel support barcode scanning? | Excel Discussion (Misc queries) | |||
After scanning a barcode how can I automatically move to the next. | Excel Discussion (Misc queries) | |||
Textbox & Barcode Scanning | Excel Programming |