Search for catergory name and add 1 to the next cell

instanceoftime

Board Regular
Joined
Mar 23, 2011
Messages
103
Maybe direct me to a better forum as would be really great to have it online but here goes. (Been out of the vba game for way too long to remember even the simplest things)

I have a spreadsheet with 2 columns Column A has names (0002, 0003, 0004, 0005...) as text Column B will just have a number.

I just want to use a simplistic inventory system. Pallet comes in and is slapped with a barcode matching one of the values above.

I would like to scan the barcode, search the column A for it and add +1 to the value next to it in B.

I will be using a user form and activating by the click response from the scan gun.

txtbxCategory will have the value (and column A will have no more than 20 values but maybe less).



and reversely I will have another button to reverse this on outgoing and want to subtract a scanned item.

it's been 20 years and I can't even come up with starting code. My apologies
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
How about this?
Insert into your 2 command buttons on the UserForm.

One for adding.
VBA Code:
Private Sub cmdAddItem_Click()
   Dim barcode As String
   Dim searchRange As Range
   Dim cell As Range
 
    barcode = txtbxCategory.Value
   Set searchRange = Range("A:A") ' Adjust the range as needed.
 
    For Each cell In searchRange
       If cell.Value = barcode Then
           cell.Offset(0, 1).Value = cell.Offset(0, 1).Value + 1 ' Increment the count in column B.
           Exit For
       End If
   Next cell
End Sub

One for subtracting.
VBA Code:
Private Sub cmdSubtractItem_Click()
   Dim barcode As String
   Dim searchRange As Range
   Dim cell As Range
 
    barcode = txtbxCategory.Value
   Set searchRange = Range("A:A") ' Adjust the range as needed.
 
    For Each cell In searchRange
       If cell.Value = barcode Then
           If cell.Offset(0, 1).Value > 0 Then
               cell.Offset(0, 1).Value = cell.Offset(0, 1).Value - 1 ' Decrement the count in column B.
           End If
           Exit For
       End If
   Next cell
End Sub
 
Upvote 0
I'm doing 2 seperate forms (one when scanning incoming and one for scanning outgoing). I may combine them and prompt the user to choose.
Right now I just have a simple form with a textbox and command button.

I used the code below and works as it should but it isn't registering the first scangun click?

VBA Code:
Private Sub UserForm_Initialize()



    ' Set focus to txtbxOutgoing

    txtbxOutgoing.SetFocus 



End Sub



Private Sub cmdRemoveOne_Click()

    Dim Barcode As String

    Dim FoundCell As Range

    Dim CorrespondingText As String

    Dim LogSheet As Worksheet



    Set LogSheet = ThisWorkbook.Sheets("Sheet1")

  

    'scanning item

    Barcode = txtbxOutgoing.Text

  

    ' Exit if user cancels input

    If Barcode = "" Then Exit Sub

  

    ' Search for the input in Column A up to A15

    Set FoundCell = Sheets("Sheet1").Range("A1:A15").Find(What:=Barcode, LookIn:=xlValues, LookAt:=xlWhole)

  

    ' If a match is found

    If Not FoundCell Is Nothing Then

        ' Get the corresponding text from Column C

        CorrespondingText = FoundCell.Offset(0, 2).Value

      

        ' Increment the corresponding value in Column B

        FoundCell.Offset(0, 1).Value = FoundCell.Offset(0, 1).Value - 1

      



        ' Log the scan in Sheet1 starting at the next available row in Column E

        LogSheet.Cells(LogSheet.Rows.Count, 5).End(xlUp).Offset(1, 0).Value = " "

        LogSheet.Cells(LogSheet.Rows.Count, 5).End(xlUp).Offset(0, 1).Value = CorrespondingText

    Else

        MsgBox "Text not found in Column A up to A15.", vbExclamation

    End If

  

    End Sub
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,224
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top