CODEWITHSUNDEEP
excelvba
user12513810
user12513810

Reputation: 13

Combine two vba codes into one

i'm new to vba and excel also,i created a database in excel and these two codes are essential for my project to work.. i want to run these vba codes in the same time, can you help me merge the code and make it work, please? i tried by myself but nothing worked.

Private Sub Worksheet_Change(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

and the second one:

Private Sub Worksheet_Change(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

Upvotes: 1

Views: 729

Answers (1)

Vityata
Vityata

Reputation: 43575

There are plenty of ways to combine two codes. Probably the best way is to rewrite everything, but it could be that there is no need or no time or no skills for that. Take a look here for some example - How to combine two vba codes?.

The laziest method, I can think of is simply mimicing the Worksheet_Change() events, passing the target to another private sub:

Private Sub Worksheet_Change(ByVal target As Range)

    FirstCode target
    SecondCode target

End Sub

Private Sub FirstCode(ByVal target As Range)
    Debug.Print target.Address & " from FirstCode()"
End Sub

Private Sub SecondCode(ByVal target As Range)
    Debug.Print target.Address & " from SecondCode()"
End Sub

Upvotes: 3

Related Questions