We help IT Professionals succeed at work.

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

Ed_CLP
Ed_CLP asked
on
Medium Priority
286 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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)
Oops a typo.

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

SearchOrder:=xlByRows

Sid

Author

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!
Gr8 :)

Sorry for the miss :)

Sid
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.