Need help to create macro

[harleyd]

I need help to create a macro to do the following:

Spreadsheet has the following columns among others

UPC Qty Ord Qty Rcvd.

I need to open a input box that will accept keyboard or scanner input.
Find the upc scanned.
Move the cursor to the qty rcvd col in the row where the upc was found, open a input box and wait for input.
When input is entered return to beginning
If upc is not found then display an msg box "Error upc xxx not found.
When ok is hit return to start.
I receive the spreadsheet already populated except for qty rcvd column which I will add before I start to process.
I would like to be able to copy the macro to any sheet or use with any open sheet.
I need to be able to change the column locations if necessary in the macro either by editing the macro or other form.
Thanks in advance for any assistance.

[Aaron Blood]

Excel doesn't automatically or by macro accept scanner input. You'll need some specialized driver software for that.

The macro to find a given number is fairly simple, but before we spend any time on it, you need to track down that scanner driver (which will presumably include some sort of Excel interface). I haven't messed with scanner readers, perhaps someone else can recommend one.

[harleyd]

The scanner is a keyboard wedge type that simply reads the barcode, inserts a carriage return (enter) and sends is down the keyboard cable. Excel doesn't know that the input is not keyboard. I have already used the scanner to input numbers to excel and it works well.

[Killian]

So something like this would do it...


Sub GetInput()
Dim UPCCode As String
Dim rng As Range, Target As Range
UPCCode = InputBox("Enter UPC code:")
Do While UPCCode <> "" 'cancel or OK while blank to quit loop
'I'm assuming data starts in A1, there are no blank rows and UPC code is in column 1
Set rng = ActiveSheet.Range("A1").CurrentRegion.Columns(1)
Set Target = rng.Find(UPCCode, LookIn:=xlValues)
If Not Target Is Nothing Then
'here I'm assumming the qty received column is the UPC code column + 2
Target.Offset(0, 2).Value = InputBox("Enter quantity recieved:")
Else
MsgBox UPCCode & " not found."
End If
UPCCode = InputBox("Enter UPC code:")
Loop
End Sub

[harleyd]

Thanks, that works just perfect for me.