Need help Using three independent VBA codes on the same Sheet

IanCPL

New Member
Joined
May 2, 2017
Messages
13
This is my Current line of Code on my sheet. Its purpose it to autofill a blank cell with a remaining value. My goal is to apply this same code two more times for two other independent range of cells. The Second range of cells is K4, N4, and P4, and the Third Range of cells is V4, AC4, AG4, AK4, and AO4. How would i go about combining the code so it can individually apply to all three ranged cells?


Code:
Private Sub Worksheet_Change(ByVal Target As Range)Const csMONITOR_RANGE    As String = "C4,F4,I4"
Dim rMonitor             As Range
Dim vValue
    If Not Intersect(Target, Range(csMONITOR_RANGE)) Is Nothing Then
        Set rMonitor = Range(csMONITOR_RANGE)
        
        If Intersect(Target, rMonitor).Cells.Count < 3 Then
            If Application.Count(rMonitor) = 2 Then
                Application.EnableEvents = False
                rMonitor.SpecialCells(xlCellTypeBlanks).Value = 1 - Application.Sum(rMonitor)
                Application.EnableEvents = True
            ElseIf Application.Count(rMonitor) = 3 Then
                vValue = Intersect(Target, rMonitor).Value
                Application.EnableEvents = False
                rMonitor.ClearContents
                Intersect(Target, rMonitor).Value = vValue
                Application.EnableEvents = True
            End If
        End If
    End If
End Sub
 
Last edited by a moderator:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Something like
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Const csMONITOR_RANGE    As String = "C4,F4,I4"
Const csMONITOR_RANGE2   As String = "K4,N4,P4"
Const csMONITOR_RANGE3   As String = ""
Dim rMonitor             As Range
Dim vValue
    If Not Intersect(Target, Range(csMONITOR_RANGE)) Is Nothing Then
        Set rMonitor = Range(csMONITOR_RANGE)
        
        If Intersect(Target, rMonitor).Cells.Count < 3 Then
            If Application.Count(rMonitor) = 2 Then
                Application.EnableEvents = False
                rMonitor.SpecialCells(xlCellTypeBlanks).Value = 1 - Application.Sum(rMonitor)
                Application.EnableEvents = True
            ElseIf Application.Count(rMonitor) = 3 Then
                vValue = Intersect(Target, rMonitor).Value
                Application.EnableEvents = False
                rMonitor.ClearContents
                Intersect(Target, rMonitor).Value = vValue
                Application.EnableEvents = True
            End If
        End If
    ElseIf Not Intersect(Target, Range(csMONITOR_RANGE2)) Is Nothing Then
        'your
        'code
        'here
    ElseIf Not Intersect(Target, Range(csMONITOR_RANGE3)) Is Nothing Then
        'your
        'code
        'here
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,315
Members
452,634
Latest member
cpostell

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