Merging VBA Code together?

vbabasic

New Member
Joined
Sep 22, 2021
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
Forgive me, because the past two weeks have been my first venture into VBA, so I'm very new at this (and this forum, which has been super helpful!).

If I understand correctly, you cannot add two separate instances of something like...

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

...because it will throw an "ambiguous" error.

So then, how do you successfully merge two separate of these codes without them interfering with each other?

I have a ton of different codes going on in multiple sheets as well as the Workbook which are all currently working via mostly trial and error, but I've tried to add in this one tiny piece of code to an existing Worksheet_Change, but it either doesn't work or breaks everything else (or both).

Example of script I want to add:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
    If Cell.Column = Range("AR:AR").Column Then
        If Cell.Value <> "" Then
            Cells(Cell.Row, "F").Value = Int(Now)
        Else
            Cells(Cell.Row, "F").Value = ""
        End If
    End If
Next Cell
End Sub

Example of existing script:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AP:AP")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets("Former").Cells(Rows.Count, "AP").End(xlUp).Row + 1
  On Error GoTo ErrLoc
  
Application.EnableEvents = False
ActiveWorkbook.Save

If Target.Value = "Clear" Then
Rows(Target.Row).Copy Destination:=Sheets("Former").Rows(Lastrow)

End If

If Target.Value = "Remove" Then
Rows(Target.Row).Copy Destination:=Sheets("Former").Rows(Lastrow)
Rows(Target.Row).Delete
End If
End If

ErrLoc:
Application.EnableEvents = True

End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

vbabasic

New Member
Joined
Sep 22, 2021
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
For this one, I ended up just doing a function module which works well enough for what I'm trying to achieve, but I'm still curious as to how I could have made the above work.

VBA Code:
Function Updating_Date(dependent_cell As Range) As Date
  Updating_Date = Date
End Function

Excel Formula:
=Updating_Date(F4)
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,046
Office Version
  1. 2019
Platform
  1. Windows
Hi,
not fully tested but see if this update to your original idea does what you want

VBA Code:
Option Base 1
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Lastrow     As Long
    Dim m           As Variant
    Dim Cell        As Range, rng As Range
    Dim wsFormer    As Worksheet
    
    On Error GoTo ErrLoc
    
    Set wsFormer = ThisWorkbook.Worksheets("Former")
    
    Set rng = Me.Range("AP:AP,AR:AR")
    
    If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Err.Raise 70
    
    If Not Intersect(Target, Me.Range("AP:AP,AR:AR")) Is Nothing Then
        Application.EnableEvents = False
        
        If Not Intersect(Target, rng.Areas(1)) Is Nothing Then
        
            'column AP
            ActiveWorkbook.Save
            
            m = Application.Match(Target.Value, Array("Clear", "Remove"), 0)
            Lastrow = wsFormer.Cells(wsFormer.Rows.Count, "AP").End(xlUp).Row + 1
            
            Me.Rows(Target.Row).Copy Destination:=wsFormer.Rows(Lastrow)
            If Not IsError(m) And m = 2 Then Me.Rows(Target.Row).Delete
            
        Else
        
            'column AR
            Me.Cells(Target.Row, "F").Value = Int(Now)
            
        End If
    End If
    
ErrLoc:
    Application.EnableEvents = True
    
End Sub

Always make a backup before testing new code

Hope helpful

Dave
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,036
Messages
5,767,766
Members
425,431
Latest member
Sayson

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
Top