Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-04-20
5
Medium Priority
?
277 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
0
Comment
Question by:Ed_CLP
  • 3
  • 2
5 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35434550
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
 

Author Comment

by:Ed_CLP
ID: 35434821
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35434914
Oops a typo.

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

SearchOrder:=xlByRows

Sid
0
 

Author Comment

by:Ed_CLP
ID: 35435314
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35435337
Gr8 :)

Sorry for the miss :)

Sid
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question