Macro sub that inserts formula for an entire range ?

[mphansen]

I have the following code:

Sub Last()
Range("A65536").End(xlUp).Offset(0, 1).Select
End Sub
Sub update()
'
' update Macro
' Macro recorded 9/23/02 by me04518
'
ActiveCell.FormulaR1C1 = _
"=IF(OR(ISERROR(MATCH(R1C,'[Master Performance Sheet.xls]Manager'!C3,0)),R1C=""""),"""",INDEX('[Master Performance Sheet.xls]Manager'!C1:C7,MATCH(R1C,'[Master Performance Sheet.xls]Manager'!C3,0),6))"
End Sub

Sub update1()
'
' update1 Macro
' Macro recorded 9/23/02 by me04518
'
'
Application.Run "'Manager Analysis Macro.xls'!Last"
Application.Run "'Manager Analysis Macro.xls'!update"
End Sub

My problem is that I need one of the subs to also enter the formula into C13, D13,....where any name in Row 1:1 is present. The current code inserts the formula into B13. How can I do this ?

THank you - Mark

BTW - is there a better way to condense Sub Last(), Sub Update() & Sub Update1()

Book1
	A	B	C	D	E	F	G
1	$1:$301	Agnos	Agger	Bison	Berk	Dom	Dirk
2	Oct-01	-0.82%	-0.76%	3.55%	-1.73%	5.35%	-7.22%
3	Nov-01	1.02%	1.04%	2.77%	1.12%	1.57%	-5.90%
4	Dec-01	-0.12%	-0.15%	3.25%	0.89%	0.52%	-1.11%
5	Jan-02	0.55%	0.54%	1.57%	3.51%	1.90%	-1.00%
6	Feb-02	0.67%	0.68%	0.52%	-1.43%	-4.26%	1.90%
7	Mar-02	0.20%	0.19%	1.25%	3.71%	2.12%	-0.80%
8	Apr-02	0.83%	0.85%	1.76%	3.27%	1.29%	5.80%
9	May-02	1.01%	0.91%	1.21%	0.21%	-2.17%	1.90%
10	Jun-02	0.41%	0.41%	-1.00%	-1.64%	-9.44%	6.20%
11	Jul-02	1.07%	1.07%	-0.25%	-4.27%	-4.19%	4.30%
12	Aug-02	0.51%	0.52%	1.60%	-4.37%	6.05%	1.10%
13	Sep-02
ReturnData

 

[mphansen]

Cells A1, A2 and A3 of Sheet1 contain dates,
A1 = last month (9/1/02)
A2 = This month (10/1/02)
A3 = cutoff date for update = (10/12/02)

 

[Andrew Poulsom]

You can't select the range because the sheet isn't active. It's an irritating "feature" and the error message is, as usual, far from helpful.

However, there is no need to select a range to work with it:

Sub Testmonthlies()
'''''''''''''''''''''''''''''''
'Pulls in monthly & midmonth returns from Master Performance Sheet.
''''''''''''''''''''''''''''''''
    Dim LastCol As Integer
    LastCol = Sheet2.Range("IV1").End(xlToLeft).Column
    Dim x As Long, Rng As Range
    Dim RecentMonth As Date
    Dim MonthInsert As Integer
    Set Rng = Sheet2.Range("A:A")

'   Date parameters
    If Date <= Sheet1.Range("A3") Then
        RecentMonth = Sheet1.Range("A1")
    Else
        RecentMonth = Sheet1.Range("A2")
    End If

'   Identifies the date parameter met and selects applicable row
    MonthInsert = Application.Match(CLng(RecentMonth), Rng, 0)
    If IsError(MonthInsert) Then
        MsgBox "More months need to be added in Column A"
        Exit Sub
    Else
        With Sheet2.Range("A1").Offset(MonthInsert - 1, 1).Resize(, LastCol - 1)
'           formula to retrieve returns from Master Performance Sheet
            .FormulaR1C1 = _
            "=IF(OR(ISERROR(MATCH(R1C,'[Master Performance Sheet.xls]Manager'!C3,0)),R1C=""""),"""",INDEX('[Master Performance Sheet.xls]Manager'!C1:C7,MATCH(R1C,'[Master Performance Sheet.xls]Manager'!C3,0),6))"
            .Value = .Value
            .NumberFormat = "0.00%"
        End With
    End If
End Sub

I also removed the Copy/Paste bit. It is more efficient to just make the cell's value = its value.

You will have to put the folder name back in your formula.

 

[mphansen]

That worked, & the command button works too now ! Didn't realize that Select only works on the active sheet.

Noticed a big change in speed when I changed copy/paste to .Value = .Value

Many thanks along the way Andrew.