Combining VBA code together

**kohli.jd** · 03-21-2024, 01:29 PM

Can someone please help me merge, marry, combine, etc. however you would call it these 2 lines of code together? I know it's not hard to do but I am not smart enough to figure it out by myself.
FIRST section of code:

Const dd_max As Integer = 36 '   <<< number of datavalidations

    With ActiveSheet
        With Target
            If .Count > 1 Then Exit Sub
            If .Column <> 4 Then Exit Sub
            ay = .Row
            dd = (ay - 15) / 7
            If dd < 1 Then Exit Sub
            If dd > dd_max Then
                With Application
                    .EnableEvents = False
                    Beep
                    .Undo
                    .EnableEvents = True
                    Exit Sub
                End With
            End If
            ay0 = ay + 11
            ay1 = ay + 14
            ay2 = ay + 13
            ay3 = ay + 18
        End With
     
        With Application
            .DisplayAlerts = False
            .EnableEvents = False
        End With
     
        If (ay - 22) Mod 7 = 0 Then
                bg = .Range("D22").Interior.Color
                With .Range(.Cells(ay, "D"), .Cells(ay3, "K"))
                    .UnMerge
                    .Borders.LineStyle = xlNone
                    .Interior.ColorIndex = xlNone
                End With
                With .Range(.Cells(ay, "N"), .Cells(ay3, "N"))
                    .UnMerge
                    .Borders.LineStyle = xlNone
                    .Interior.ColorIndex = xlNone
                End With
             
                For y = 0 To 2
                    dv = "1,2,3"
                    If dd + y > dd_max - 2 Then dv = "1,2"
                    If dd + y > dd_max - 1 Or Target.Value <> 1 Then dv = "1"
                    If dd + y <= dd_max Then
                        With .Cells(ay + y * 7, "D").Validation
                            .Delete
                            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=dv
                            .IgnoreBlank = True: .InCellDropdown = True
                            .ShowInput = True:  .ShowError = True
                        End With
                    End If
                Next y
             
                Select Case Target.Value
                    Case 1
                        For y = 2 To 0 Step -1
                            If dd + y <= dd_max Then
                                ayy7 = ay + y * 7
                                With .Range(.Cells(ayy7, "E"), .Cells(ayy7 + 4, "K"))
                                    .Merge
                                    .BorderAround , ColorIndex:=5, Weight:=xlThin
                                End With
                                With .Range(.Cells(ayy7, "D"), .Cells(ayy7 + 4, "D"))
                                    .Merge
                                    .BorderAround , ColorIndex:=5, Weight:=xlMedium
                                    .Interior.Color = bg
                                End With
                                With .Range(.Cells(ayy7, "N"), .Cells(ayy7 + 4, "N"))
                                    .Merge
                                    .BorderAround , ColorIndex:=5, Weight:=xlMedium
                                    .Interior.Color = bg
                                End With
                                .Cells(ayy7, "D") = 1
                                .Cells(ayy7, "E") = "Heat Pump"
                                .Cells(ayy7, "N") = "15AMP"
                            End If
                        Next y
                    Case 2
                            With .Range(.Cells(ay, "E"), .Cells(ay0, "K"))
                                .Merge
                                .BorderAround , ColorIndex:=5, Weight:=xlThin
                            End With
                            With .Range(.Cells(ay, "D"), .Cells(ay0, "D"))
                                .Merge
                                .BorderAround , ColorIndex:=5, Weight:=xlMedium
                                .Interior.Color = bg
                            End With
                            With .Range(.Cells(ay, "N"), .Cells(ay0, "N"))
                                .Merge
                                .BorderAround , ColorIndex:=5, Weight:=xlMedium
                                .Interior.Color = bg
                            End With
                            .Cells(ay, "D") = 2
                            .Cells(ay, "N") = "15AMP"
                     
                        If dd + 1 < dd_max Then
                            With .Range(.Cells(ay1, "E"), .Cells(ay3, "K"))
                                .Merge
                                .BorderAround , ColorIndex:=5, Weight:=xlThin
                            End With
                            With .Range(.Cells(ay1, "D"), .Cells(ay3, "D"))
                                .Merge
                                .BorderAround , ColorIndex:=5, Weight:=xlMedium
                                .Interior.Color = bg
                            End With
                            With .Range(.Cells(ay1, "N"), .Cells(ay3, "N"))
                                .Merge
                                .BorderAround , ColorIndex:=5, Weight:=xlMedium
                                .Interior.Color = bg
                            End With
                            .Cells(ay1, "D") = 1
                            .Cells(ay1, "N") = "15AMP"
                        End If
                    Case 3
                        With .Range(.Cells(ay, "E"), .Cells(ay3, "K"))
                            .Merge
                            .BorderAround , ColorIndex:=5, Weight:=xlThin
                        End With
                        With .Range(.Cells(ay, "D"), .Cells(ay3, "D"))
                            .Merge
                            .BorderAround , ColorIndex:=5, Weight:=xlMedium
                            .Interior.Color = bg
                        End With
                        With .Range(.Cells(ay, "N"), .Cells(ay3, "N"))
                            .Merge
                            .BorderAround , ColorIndex:=5, Weight:=xlMedium
                            .Interior.Color = bg
                        End With
                End Select
                .Cells(ay - 1, "D").Activate
        Else
            With Application
                Beep
                .EnableEvents = False
                .Undo
            End With
        End If
    End With
    With Application
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With ActiveSheet
        With Target
            If .Column <> 4 Then Exit Sub
            ay = .Row
            yx = .Value
            dd = (ay - 15) / 7
            If dd < 1 Then Exit Sub
            If dd > dd_max Then
                Beep
                Exit Sub
            End If
        End With
 
        If (ay - 22) Mod 7 = 0 Then
            If yx(1, 1) = Empty Then
                With .Cells(ay, "D").Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1"
                    .IgnoreBlank = True: .InCellDropdown = True
                    .ShowInput = True:  .ShowError = True
                End With
            Else
                If yx(1, 1) <> 1 Then .Cells(ay, "D") = 1
            End If
        End If
    End With
End Sub

SECOND portion of code:

Const dd_max As Integer = 36 '   <<< number of datavalidations
    
    With ActiveSheet
        With Target
            If .Count > 1 Then Exit Sub
            If .Column <> 52 Then Exit Sub
            ay = .Row
            dd = (ay - 15) / 7
            If dd < 1 Then Exit Sub
            If dd > dd_max Then
                With Application
                    .EnableEvents = False
                    Beep
                    .Undo
                    .EnableEvents = True
                    Exit Sub
                End With
            End If
            ay0 = ay + 11
            ay1 = ay + 14
            ay2 = ay + 13
            ay3 = ay + 18
        End With
        
        With Application
            .DisplayAlerts = False
            .EnableEvents = False
        End With
        
        If (ay - 22) Mod 7 = 0 Then
                
                With .Range(.Cells(ay, "AZ"), .Cells(ay3, "AS"))
                    .UnMerge
                    .Borders.LineStyle = xlNone
                End With
                With .Range(.Cells(ay, "AP"), .Cells(ay3, "AP"))
                    .UnMerge
                    .Borders.LineStyle = xlNone
                End With
                
                For y = 0 To 2
                    dv = "1,2,3"
                    If dd + y > dd_max - 2 Then dv = "1,2"
                    If dd + y > dd_max - 1 Or Target.Value <> 1 Then dv = "1"
                    If dd + y <= dd_max Then
                        With .Cells(ay + y * 7, "AZ").Validation
                            .Delete
                            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=dv
                            .IgnoreBlank = True: .InCellDropdown = True
                            .ShowInput = True:  .ShowError = True
                        End With
                    End If
                Next y
                
                Select Case Target.Value
                    Case 1
                        For y = 0 To 2
                            If dd + y <= dd_max Then
                                ayy7 = ay + y * 7
                                With .Range(.Cells(ayy7, "AZ"), .Cells(ayy7 + 4, "AZ"))
                                    .Merge
                                    .BorderAround , ColorIndex:=1, Weight:=xlMedium
                                End With
                                With .Range(.Cells(ayy7, "AY"), .Cells(ayy7 + 4, "AS"))
                                    .Merge
                                    .BorderAround , ColorIndex:=1, Weight:=xlMedium
                                End With
                                With .Range(.Cells(ayy7, "AP"), .Cells(ayy7 + 4, "AP"))
                                    .Merge
                                    .BorderAround , ColorIndex:=1, Weight:=xlThin
                                End With
                                .Cells(ayy7, "AZ") = 1
                                .Cells(ayy7, "AY") = "Heat Pump"
                                .Cells(ayy7, "AP") = "15AMP"
                            End If
                        Next y
                    Case 2
                            With .Range(.Cells(ay, "AZ"), .Cells(ay0, "AZ"))
                                .Merge
                                .BorderAround , ColorIndex:=1, Weight:=xlMedium
                            End With
                            With .Range(.Cells(ay, "AY"), .Cells(ay0, "AS"))
                                .Merge
                                .BorderAround , ColorIndex:=1, Weight:=xlMedium
                            End With
                            With .Range(.Cells(ay, "AP"), .Cells(ay0, "AP"))
                                .Merge
                                .BorderAround , ColorIndex:=1, Weight:=xlThin
                            End With
                            .Cells(ay, "AZ") = 2
                            .Cells(ay, "AP") = "15AMP"
                        
                        If dd + 1 < dd_max Then
                            With .Range(.Cells(ay1, "AZ"), .Cells(ay3, "AZ"))
                                .Merge
                                .BorderAround , ColorIndex:=1, Weight:=xlMedium
                            End With
                            With .Range(.Cells(ay1, "AY"), .Cells(ay3, "AS"))
                                .Merge
                                .BorderAround , ColorIndex:=1, Weight:=xlMedium
                            End With
                            With .Range(.Cells(ay1, "AP"), .Cells(ay3, "AP"))
                                .Merge
                                .BorderAround , ColorIndex:=1, Weight:=xlThin
                            End With
                            .Cells(ay1, "AZ") = 1
                            .Cells(ay1, "AP") = "15AMP"
                        End If
                    Case 3
                        With .Range(.Cells(ay, "AZ"), .Cells(ay3, "AZ"))
                            .Merge
                            .BorderAround , ColorIndex:=1, Weight:=xlMedium
                        End With
                        With .Range(.Cells(ay, "AY"), .Cells(ay3, "AS"))
                            .Merge
                            .BorderAround , ColorIndex:=1, Weight:=xlMedium
                        End With
                        With .Range(.Cells(ay, "AP"), .Cells(ay3, "AP"))
                            .Merge
                            .BorderAround , ColorIndex:=1, Weight:=xlThin
                        End With
                End Select
        Else
            With Application
                Beep
                .EnableEvents = False
                .Undo
            End With
        End If
    End With
    With Application
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With ActiveSheet
        With Target
            If .Count > 1 Then Exit Sub
            If .Column <> 52 Then Exit Sub
            ay = .Row
            yx = .Value
            dd = (ay - 15) / 7
            If dd < 1 Then Exit Sub
            If dd > dd_max Then
                Beep
                Exit Sub
            End If
        End With
    
        If (ay - 22) Mod 7 = 0 Then
            If yx = Empty Then
                With .Cells(ay, "AZ").Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1"
                    .IgnoreBlank = True: .InCellDropdown = True
                    .ShowInput = True:  .ShowError = True
                End With
            End If
        End If
    End With
End Sub

**6StringJazzer** · 03-21-2024, 02:47 PM

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

I am providing the link this time because you are a new user.

https://www.mrexcel.com/board/thread...-help.1255986/

**kohli.jd** · 03-21-2024, 02:56 PM

I am so sorry! I will read the rules!

**kohli.jd** · 03-25-2024, 10:56 AM

Anyone? No insight on this request?

**6StringJazzer** · 03-25-2024, 11:07 AM

In both cases you do not show the declaration of the Sub at the top of the code.

The code is very difficult to read because the variables are not declared, the variable names are cryptic, and there are no comments. Just speaking for myself it would take me way more time just to understand what this code does than I can offer for free. And that doesn't even get into how to combine them.

If the first sub is Worksheet_Change you can take the same approach as I have done below.

Here is a quick combination of the two Worksheet_SelectionChange events without needing to understand what any of it does. By doing analysis of the functionality it might be possible to make it more efficient or more readable. It's impossible to test without your file.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    With ActiveSheet
    
        If Target.Column = 52 And Target.Count = 1 Then
        
          With Target
              ay = .Row
              yx = .Value
              dd = (ay - 15) / 7
              If dd < 1 Then Exit Sub
              If dd > dd_max Then
                  Beep
                  Exit Sub
              End If
          End With
      
          If (ay - 22) Mod 7 = 0 Then
              If yx = Empty Then
                  With .Cells(ay, "AZ").Validation
                      .Delete
                      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1"
                      .IgnoreBlank = True: .InCellDropdown = True
                      .ShowInput = True:  .ShowError = True
                  End With
              End If
          End If
        
        ElseIf Target.Column = 4 Then
        
          With Target
              ay = .Row
              yx = .Value
              dd = (ay - 15) / 7
              If dd < 1 Then Exit Sub
              If dd > dd_max Then
                  Beep
                  Exit Sub
              End If
          End With
      
          If (ay - 22) Mod 7 = 0 Then
              If yx = Empty Then
                  With .Cells(ay, "AZ").Validation
                      .Delete
                      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1"
                      .IgnoreBlank = True: .InCellDropdown = True
                      .ShowInput = True:  .ShowError = True
                  End With
              End If
          End If
          
      End If
        
    End With
End Sub

Combining VBA code together

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Combining VBA code together

Re: Combining VBA code together

Re: Combining VBA code together

Re: Combining VBA code together

Re: Combining VBA code together

Thread Information

Users Browsing this Thread

Similar Threads

Combining two VBA code

Combining code

Combining value in one vba code

[SOLVED] Help combining code

[SOLVED] combining code

combining code

Combining Code

Bookmarks

Bookmarks

Posting Permissions