VBA Name Column

[th259]

I have the following code to add a new column after the DateThru column:

Rows(1).Find("DateThru").Offset(, 1).EntireColumn.Insert

How do I name the new column?

I also have the following

'SUMPRODUCT Forumla in Column L to find overlapping dates
With ws
Dim LR As Long, _
DateFrom As Range, _
DateThru As Range, _
EIN As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
Set DateFrom = Range("J2:J" & LR)
Set DateThru = Range("K2:K" & LR)
Set EIN = Range("A2:A" & LR)
Range("L2").Formula = "=SUMPRODUCT((J2<=" & DateThru.Address & ")*(K2>=" & DateFrom.Address & ")*(a2=" & EIN.Address & "))>1"
Range("L2:L" & LR).FillDown
End With

How do I set the range and formula base on the column name instead of the cell number?

 

[Herakles]

I have the following code to add a new column after the DateThru column:



How do I name the new column?

I also have the following


How do I set the range and formula base on the column name instead of the cell number?

Do these work?

Test them on a copy of your data.

Private Sub subInsertColumn()
Dim rngFound As Range

    Set rngFound = ActiveSheet.Rows(1).Find("DateThru", LookIn:=xlValues)
    
    If Not rngFound Is Nothing Then
            
        rngFound.Offset(0, 1).EntireColumn.Insert
        
        rngFound.Offset(0, 1).Value = "New Column Name"

    End If

End Sub

Private Sub subInsertFormula()
Dim Ws As Worksheet
Dim lngLR As Long
    
    Set Ws = ActiveSheet
    
    With Ws
        
        lngLR = .Range("A" & Rows.Count).End(xlUp).Row
            
        .Range("L2:L" & lngLR).Formula = "=SUMPRODUCT((J2<=" & .Range("K2:K" & lngLR).Address & ")*(K2>=" & .Range("J2:J" & _
            lngLR).Address & ")*(A2=" & .Range("A2:A" & lngLR).Address & "))>1"
    
    End With

End Sub

 

[th259]

Thank you! I tried it and it worked once but when I tried it again it freezes my computer.