?
Solved

Create calculated column on multiple worksheets

Posted on 2011-03-21
6
Medium Priority
?
241 Views
Last Modified: 2012-05-11
I need to add a calculated column to the right of the last column in several populated worksheets (in this case it is sheets 1 thru 5 from the same workbook).
I would like to create a column named Month_Year based on this formula  DATE(H2|I2|1)
That creates the same number of rows as in the column to the immediate left of the calculated column.  Worksheets 1-5 contain different numbers of columns and rows of data.
0
Comment
Question by:Ed_CLP
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:ragnarok89
ID: 35181524
This should work.
Sub FindLastColumn()
'Finds last used column, copies and selects the next
Dim LastColumn As Integer
'Find last column with text
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Columns.
        LastColumn = Cells.Find(What:="*", After:=[A1], _
                           SearchOrder:=xlByColumns, _
                       SearchDirection:=xlPrevious).Column
    End If
    
'se;ect first row in last column
cells(1,lastcolumn).select

-- do other stuff ---

End Sub

Open in new window

0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 35181715
Hi,

may be...


Kris
Sub kTest()
    Dim i As Long, r   As Long, c As Long, MySheets
    
    MySheets = Array("Sheet1", "Sheet2", "Sheet3") '<<=== add more sheets
    
    For i = 0 To UBound(MySheets)
        With Worksheets(CStr(MySheets(i)))
            r = .Range("h" & .Rows.Count).End(xlUp).Row
            c = .UsedRange.Find("*", .[a1], , , , 2).Column
            .Cells(2, c + 1).Resize(r - 1).Formula = "=date(h2,i2,1)"
        End With
    Next
    
End Sub

Open in new window

0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 35181752
Hi,

replace

c = .UsedRange.Find("*", .[a1], , , , 2).Column

with

c = .UsedRange.Find("*", .[a1], , , 2, 2).Column

Kris
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Ed_CLP
ID: 35182019
@krishnakrkc - that is perfect!  Can the column title(Month_year) be set as part of the script?
0
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 2000 total points
ID: 35182092
Hi,

Try


Kris
Sub kTest()
    Dim i As Long, r   As Long, c As Long, MySheets
    
    MySheets = Array("Sheet1", "Sheet2", "Sheet3") '<<=== add more sheets
    
    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
    
End Sub

Open in new window

0
 

Author Closing Comment

by:Ed_CLP
ID: 35182206
Completely answered my question.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

762 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