Determine the correct column by a prefix with barcodes in Excel

Описание: Determine the correct column by a prefix with barcodes in Excel. Select the correct cell to enter data automatically. No more errors is wrong cell selection in your data

Free templates and templates with code are available for purchase for $50 USD
https://www.easyexcelanswers.com/temp...
Addition Templates may be available on request

Check out my introduction to VBA    • Introduction to VBA for Excel  

Click this link to check out my one-on-one training http://www.calendly.com/easyexcelanswers

For more help visit my website www.easyexcelanswers.com or email me at [email protected].

Contact me regarding customizing this template for your needs.

Click for online Excel Consulting http://www.calendly.com/easyexcelanswers

I am able to provide online help on your computer at a reasonable rate.


I use Tube Buddy to help promote my videos
Check them out
https://www.Tubebuddy.com/easyexcelan...

Follow me on Facebook
  / easyexcel.answers  

IG @barbhendersonconsulting

You can help and generate a translation to you own language
http://www.youtube.com/timedtext_cs_p...

*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I've used or have experience with.

How to insert VBA code in Excel    • How to insert VBA code in Excel  

Sub diffscan()
Dim InputData As String
Dim Prefix As String
Dim worder As String
Dim TargetColumn As Integer
Dim r As Long


InputData = Sheet1.Range("B1").Value
'Check if there is a barcode in B1
If InputData = "" Then Exit Sub



' Check the first character (The Prefix)
Prefix = Left(InputData, 1)
worder = Mid(InputData, 2) ' The barcode without the prefix

' find column
Select Case Prefix
Case "!": TargetColumn = 2 ' Scanner 1 - Column B
Case "@": TargetColumn = 3 ' Scanner 2 - Column C
Case "#": TargetColumn = 4 ' Scanner 3 - Column D
Case "&": TargetColumn = 5 ' Scanner 4 - Column E
Case "?": TargetColumn = 6 ' Scanner 5 - Column F
Case "^": TargetColumn = 7 ' Scanner 6 - Column G
End Select


Dim rng As Range
Dim actr As Long
'find workorder row
Set rng = Sheet1.Columns("A:A").Find(What:=worder, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
MsgBox "work order not found"
Else
actr = rng.Row
End If
'select the correct row and column
Cells(actr, TargetColumn).Select
'change the font so the date will not display as a barcode
ActiveCell.Font.Name = "Calibri"
ActiveCell.Value = Date & " " & Time
ActiveCell.NumberFormat = "m/d/yyyy h:mm AM/PM"


' clear the barcode scanning cell
Sheet1.Range("B1").ClearContents
Sheet1.Range("B1").Select ' select the barcode scanning cell for the next scan
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("B1")) Is Nothing Then

Call diffscan
Application.EnableEvents = True
End If

End Sub