Macro sub that inserts formula for an entire range ?

mphansen · Sep 23, 2002

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%

-1.00%

-1.64%

-9.44%

6.20%

11

Jul-02

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

Andrew Poulsom · Sep 24, 2002

A few questions:

1. What is in [Master Performance Sheet.xls]Manager'!C3?
2. Why are you using MATCH on it? MATCH is normally used on a range not a single cell.
3. Doesn't your INDEX formula return a #REF! error? There is only one column, but you are referencing column 6.
4. Is your real purpose to find in range C1:C7 what is in row 1 of columns B:G and return what is in range H1:H7?

mphansen · Sep 24, 2002

Andrew - finally had a chance to see your reply. My replies to your questions are below.

1. What is in [Master Performance Sheet.xls]Manager'!C3?

In this sheet called (Retrieval) are names that also appear in the sheet Master Perf.. (Source)

2. Why are you using MATCH on it? MATCH is normally used on a range not a single cell.

I've used match to determine first if the name is present in the Source sheet and if not = blank, and second, if there is no name in the row the formula references, then = blank

3. Doesn't your INDEX formula return a #REF! error? There is only one column, but you are referencing column 6.

If there is a name match in the Source sheet, then the index formula pulls the data in the 6th column from the Source sheet that corresponds to the name.

4. Is your real purpose to find in range C1:C7 what is in row 1 of columns B:G and return what is in range H1:H7?

Range C1:C7 in formula notation actually refers to Range $A:$G in the Source sheet. In VBA code the "C" represents absolute referencing & 1:7 represents Columns A:G Regarding your question about Row 1 of columns B:G, that basically goes back to what my formula was doing with match & index, if no name or no match, then = "", otherwise have formula pull in data. Re: H1:H7, that column is blank

Hope this clarifies. Any insight still appreciated - Mark

Andrew Poulsom · Sep 25, 2002

Sorry to waste your time with my stupid questions - I missed the fact that the formula was all in R1C1 notation.

Anyway, this seems to work:

Code:

Sub Test()
    Range("A65536").End(xlUp).Offset(0, 1).Resize(, 6).Select
    Selection.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

mphansen · Sep 25, 2002

Hi Andrew - your last solution works perfectly - but let me ask you one last thing. My example included in the post was only a hypothetical. The real "Retrieval" sheet actually contains about 200 columns of names in row 1 and the corresponding data below those names. As names/data are added, the number of columns/data increases.

The Resize property you included is limited to the resize value...i.e. 6 in your example. How can I have the resize identify how many columns are included in row 1 and have the Resize adjust accordingly ?

I'm almost there - Mark

Andrew Poulsom · Sep 26, 2002

Like this:

Code:

Sub Test()
    Dim LastCol As Integer
    LastCol = Range("IV1").End(xlToLeft).Column
    Range("A65536").End(xlUp).Offset(0, 1).Resize(, LastCol - 1).Select
    Selection.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

mphansen · Sep 26, 2002

Andrew - perfect. One last item (I promise!) that I didn't take into account in my original post.

That items concerns Column A. Unless I (or someone else) remembers to insert an extra month every new month, the code will always paste over the last row. If I extend the dates in column A, code pastes into the last row which will not be the correct month.

I've seen some posts such as:
Application.Match(CLng(Date), Range("Sheet1!A1:A100"), 0)

Can Date in the code above be altered in some way to reflect the excel formula: DATE(YEAR(TODAY()),MONTH(TODAY()),1) = which equals current month, this case September and can this be factored into the code you had supplied ?

Sorry to drag this out -
Mark

Andrew Poulsom · Sep 27, 2002

You can just test if column B is blank, like this:

Code:

Sub Test()
    Dim LastCol As Integer
    LastCol = Range("IV1").End(xlToLeft).Column
    Range("A65536").End(xlUp).Offset(0, 1).Resize(, LastCol - 1).Select
    If IsEmpty(Selection.Cells(1.1)) Then
        Selection.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))"
    Else
        Selection.Resize(1, 1).Offset(1, -1).Select
        MsgBox "You forgot to put the new month in Column A!"
    End If
End Sub

It occurs to me that you may want to range value your formulas, because they will be invalid when you advance the month. If so insert this immediately after the line which assigns the formulas.

Code:

Dim c As Range
For Each c in Selection
    c.Value = c.Value
Next c

mphansen · Oct 2, 2002

Thanks to Andrew Poulsom's help - I've got some code that pulls values from a workbook meeting certain parameters I've defined.

There's one glitch in my code which is preventing me from using a command button located on a different sheet. The problem is that the code is running on the active sheet and I believe the troublesome line is :
Sheet2.Range("A1").Offset(MonthInsert - 1, 1).Resize(, LastCol - 1).Select

Does the problem lie in Sheet2.Range...?

Full code follows below:
Appreciate any suggestions - Mark

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
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
Sheet2.Range("A1").Offset(MonthInsert - 1, 1).Resize(, LastCol - 1).Select
End If

' formula to retrieve returns from Master Performance Sheet
Selection.FormulaR1C1 = _
"=IF(OR(ISERROR(MATCH(R1C,'E:Shared FilesPortfolio MgmtMonthly Manager Notes[Master Performance Sheet.xls]Manager'!C3,0)),R1C=""""),"""",INDEX('E:Shared FilesPortfolio MgmtMonthly Manager Notes[Master Performance Sheet.xls]Manager'!C1:C7,MATCH(R1C,'E:Shared FilesPortfolio MgmtMonthly Manager Notes[Master Performance Sheet.xls]Manager'!C3,0),6))"
With Selection
.copy
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
.NumberFormat = "0.00%"
End With
End Sub
This message was edited by mphansen on 2002-10-02 23:37

Andrew Poulsom · Oct 3, 2002

What have you got in cells A1, A2 and A3 of Sheet1?

Macro sub that inserts formula for an entire range ?

mphansen

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

mphansen

New Member

Andrew Poulsom

MrExcel MVP

mphansen

New Member

Andrew Poulsom

MrExcel MVP

mphansen

New Member

Andrew Poulsom

MrExcel MVP

mphansen

New Member

Andrew Poulsom

MrExcel MVP

Similar threads

Forum statistics

Share this page

Macro sub that inserts formula for an entire range ?

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock