• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Need to delete or over-write existing column if it already exists

I need to modify this code so that it either over-writes or deletes the Month_Year column if it already exists.  The original code finds the last column and then adds a column named Month_Year with a formula value for all its cells.

Dim i As Long, r As Long, c As Long, MySheets
   
    MySheets = Array("Sheet1", "Sheet2", "Sheet99") '<<=== adjust sheet names as needed
   
    For i = 0 To UBound(MySheets)
        With Worksheets(CStr(MySheets(i)))
            r = .Range("h" & .Rows.Count).End(xlUp).Row
            c = .UsedRange.Find("*", .[a1], , , 2, 2).Column
            .Cells(1, c + 1).Value = "Month_Year"
            .Cells(2, c + 1).Resize(r - 1).Formula = "=date(h2,i2,1)"
        End With
    Next
0
Ed_CLP
Asked:
Ed_CLP
  • 3
  • 2
1 Solution
 
SiddharthRoutCommented:
UNTESTED

I quickly wrote this. So let me know if you get any errors and I will rectify it.

Sub Sample()
    Dim i As Long, r As Long, c As Long, lastRow As Long
    Dim aCell As Range
    Dim oSht As Worksheet
    Dim strSearch As String
    Dim MySheets
    
    strSearch = "Month_Year"
    
    '~~> Adjust sheet names as needed
    MySheets = Array("Sheet1", "Sheet2", "Sheet99")
   
    For i = 0 To UBound(MySheets)
        Set oSht = Sheets(CStr(MySheets(i)))
        With oSht
            lastRow = oSht.Range("A" & Rows.Count).End(xlUp).Row

            Set aCell = oSht.Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
            LookAt:=xlWhole, leSearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            
            '~~> If found, overwrite
            If Not aCell Is Nothing Then
                c = aCell.Column
                .Cells(1, c).Value = "Month_Year"
                .Cells(2, c).Resize(r - 1).Formula = "=date(h2,i2,1)"
            Else
                r = .Range("h" & .Rows.Count).End(xlUp).Row
                c = .UsedRange.Find("*", .[a1], , , 2, 2).Column
                .Cells(1, c + 1).Value = "Month_Year"
                .Cells(2, c + 1).Resize(r - 1).Formula = "=date(h2,i2,1)"
            End If
        End With
    Next
End Sub

Open in new window


Sid
0
 
Ed_CLPAuthor Commented:
I'm getting error 448 - named argument not found in this block;

Set aCell = oSht.Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
            LookAt:=xlWhole, leSearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
0
 
SiddharthRoutCommented:
Oops a typo.

Please remove 'le' from leSearchOrder:=xlByRows so that it becomes

SearchOrder:=xlByRows

Sid
0
 
Ed_CLPAuthor Commented:
You forgot;
r = .Range("h" & .Rows.Count).End(xlUp).Row

I added it back from my original sample and now it works fine.

Thank You!
0
 
SiddharthRoutCommented:
Gr8 :)

Sorry for the miss :)

Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now