Created
December 10, 2019 21:23
-
-
Save Vitosh/2b0ccd300db89fcda80938d0057fa502 to your computer and use it in GitHub Desktop.
Gist based on StackOverflow.com question - https://stackoverflow.com/questions/59274383/combine-two-vba-codes-into-one
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Private Sub Worksheet_Change(ByVal target As Range) | |
FirstCode target | |
SecondCode target | |
End Sub | |
Private Sub FirstCode(ByVal target As Range) | |
Dim Item As String | |
Dim SearchRange As Range | |
Dim rFound As Range | |
'Don't run the macro if: | |
'Target is not a single cell: | |
If Target.Cells.Count > 1 Then Exit Sub | |
'or Target belongs to the A1.CurrentRegion: | |
If Not Intersect(Target, Range("A1").CurrentRegion) Is Nothing Then Exit Sub | |
'Avoid the endless loop: | |
Application.EnableEvents = False | |
'Looks for matches from the here first: | |
Set SearchRange = Range("A1:A" & Range("A1").CurrentRegion.Rows.Count) | |
Item = Target.Value | |
'Clears the Target: | |
Target.Value = "" | |
If Application.WorksheetFunction.CountIf(SearchRange, Item) > 0 Then | |
'There's a match already: | |
Set rFound = Columns(1).Find(What:=Item, After:=Cells(1, 1) _ | |
, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows _ | |
, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) | |
'Adds one to the Quantity: | |
rFound.Offset(0, 2).Value = rFound.Offset(0, 2).Value + 1 | |
Else | |
'Writes the value for the Barcode-list: | |
Range("A" & SearchRange.Rows.Count + 1).Value = Item | |
'Looks for the match from sheet "Inventory" column A | |
With Sheets("Inventory") | |
Set rFound = .Columns(1).Find(What:=Item, After:=.Cells(1, 1) _ | |
, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows _ | |
, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) | |
On Error GoTo 0 | |
If Not rFound Is Nothing Then | |
'Writes the Product Name and puts 1 to the Quantity column: | |
Range("B" & SearchRange.Rows.Count + 1).Value = rFound.Offset(0, 1).Value | |
Range("C" & SearchRange.Rows.Count + 1).Value = 1 | |
End If | |
End With | |
End If | |
'Enable the Events again: | |
Application.EnableEvents = True | |
End Sub | |
Private Sub SecondCode(ByVal target As Range) | |
If Target.Column <> 3 Then Exit Sub | |
If Target.Cells.Count > 1 Then Exit Sub | |
With Target.Offset(0, 3) | |
.Value = Now | |
.NumberFormat = "DD/MM/YYYY" | |
End With | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment