David has attended:
Excel Advanced course
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course
Copying a non-default Formula
Hello, I have an Excel Add-in for Morningstar, investment research analysis software. I have some data in a "Data" tab for a number of investment funds and I've created a macro that opens a new sheet for each fund.
I would also like to copy a formula but i'm not sure how. The formula is:
=MSTS(Data!C2,"NAV_daily",Data!C13,Data!C14,"CorR=C,Dates=True,Ascending=True,Freq=D,Days=T,Fill=B,Curr=BASE")
Where Data!C2 = The fund code
Data!C13/C14 = Dates
How would I copy this formula into each new sheet Cell A3? (note that Data!C2 needs to move down 1 for each sheet)
RE: Copying a non-default Formula
Hi David
Thanks for your question.
You should be able to have your macro add the formula by adding in one line
Range("C3") = "=MSTS(Data!C2,"NAV_daily",Data!C13,Data!C14,"CorR=C,Dates=True,Ascending=True,Freq=D,Days=T,Fill=B,Curr=BASE")"
I may have misunderstood your question. Let me know, thanks.
Regards
Doug Dunn
STL
RE: Copying a non-default Formula
Thanks Doug. In terms of the "Data!C2" term in the formula, can I make that a variable?
RE: Copying a non-default Formula
Hi David
I don't have access to your Add-in so my colleague Jens suggested using a basic Excel function to test out your question about including a variable.
It seems that you can by using the concatenate & as shown in my example.
The variable myNum stores the value in C2 and then an IF function tests the variable value.
Sub TestMac()
Dim MyNum As Integer
MyNum = Range("C2").Value
Range("C3") = "=IF(" & MyNum & "=1000,""Yes"",""No"")"
End Sub
You may also need to use double double quotations to make your MSTS function if referring to a text string.
Hope this helps with yours
Doug
RE: Copying a non-default Formula
Doug, thanks so far! Not sure if i've understood it all but underneath is my code.
Sub MstarCode()
'Copies Morningstar Price Excel Code
Dim Mcode As String
Dim n As Integer
For n = 2 To Sheets("Data").Range("C2").CurrentRegion.Rows.Count
Mcode = Sheets("Data").Cells(n, 3)
Range("C3").Formula = "=MSTS(Mcode,""NAV_daily"",Data!C13,Data!C14,""CorR=C,Dates=True,Ascending=True,Freq=D,Days=T,Fill=B,Curr=BASE"")"
Next n
End Sub
The problem is that it is not taking any notice of the Mcode as a variable in the formula and is just pasting it like you see it.
RE: Copying a non-default Formula
Sorry I understood what you were saying and so changed it to
Range("C3").Formula = "=MSTS(" & Mcode & ",""NAV_daily"",Data!C13,Data!C14,""CorR=C,Dates=True,Ascending=True,Freq=D,Days=T,Fill=B,Curr=BASE"")"
But now i get an debug error and I can't work out why!
RE: Copying a non-default Formula
Almost there I think.
There needs to be a " & before and & " after the variable name as below.
Range("C3").Formula = "=MSTS(" & Mcode & ",""NAV_daily"",Data!C13,Data!C14,""CorR=C,Dates=True,Ascending=True,Freq=D,Days=T,Fill=B,Curr=BASE"")"
Hope that dose it.
Doug
RE: Copying a non-default Formula
Doug,
I keep getting a debug error! :(
RE: Copying a non-default Formula
Hi David
There may be something still wron with the quotations in the part ""CorR=C...Curr=BASE""
Range("C3").Formula = "=MSTS(" & Mcode & ",""NAV_daily"",Data!C13,Data!C14,""CorR=C,Dates=True,Ascending=True,Freq=D,Days=T,Fill=B,Curr=BASE"")"
You might get an idea by recording a macro while you are typing in the MSTS function. Then looking at the recorded code.
Worth a try as it could see what's causing the debug.
Doug
RE: Copying a non-default Formula
I was having some issues with that plugin so I'm using another plugin to test and fortunately it has worked, with one minor issue.
My code is:
Sub BloombergCode()
'Copies Bloomberg Price Excel Code
For i = 2 To Sheets("Data").Range("B2").CurrentRegion.Rows.Count
Dim Bcode As String
Bcode = Sheets("Data").Cells(i, 4)
If ActiveSheet.Name = Sheets("Data").Cells(i, 2) Then
Range("A3").Formula = "=BDH(" & Bcode & ",""PX_LAST"",Data!C13,Data!C14)"
End If
Next i
End Sub
So when I am in sheet "Cautious" it returns the right code and my formula reads:
=BDH(CTRUEIR LN Equity,"PX_LAST",Data!C13,Data!C14)
The only issue is that the CTRUEIR LN Equity has to be in "" if i am not providing the cell reference.
Is there any way I can return the cell reference instead of whats in the cell?
RE: Copying a non-default Formula
Almost working but not quite!
Maybe you don't have to use a variable.
Returning to my first IF function example, there is a way to make the cell reference change without using a variable.
Sub TestMac2()
Dim i As Integer
For i = 3 To Sheets("Data").Range("B3").CurrentRegion.Rows.Count + 2
Range("A" & i) = "=if(c" & i & "=1000,""Yes"",""No"")"
Next i
End Sub
See if can be adapted fro your case.
I can't use your functions otherwise I would be able to be of more help.
Doug
