98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Copying a non-default Formula
Copying a non-default Formula
Resolved · Low Priority · Version 2013
Fri 2 Dec 2016, 12:27Delegate David said...
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)
Mon 5 Dec 2016, 14:03Trainer Doug said...
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
Tue 6 Dec 2016, 08:57Delegate David said...
RE: Copying a non-default Formula
Thanks Doug. In terms of the "Data!C2" term in the formula, can I make that a variable?
Tue 6 Dec 2016, 10:20Trainer Doug said...
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
Tue 6 Dec 2016, 14:10Delegate David said...
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.
Tue 6 Dec 2016, 14:18Delegate David said...
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!
Tue 6 Dec 2016, 14:19Trainer Doug said...
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
Tue 6 Dec 2016, 14:21Delegate David said...
RE: Copying a non-default Formula
Doug,
I keep getting a debug error! :(
Tue 6 Dec 2016, 14:34Trainer Doug said...
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
Tue 6 Dec 2016, 14:58Delegate David said...
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?
Tue 6 Dec 2016, 15:42Trainer Doug said...
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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Quickly hide and unhide rows and columnsUse the keyboard shortcut Ctrl+9 to hide selected rows and Ctrl+0 to hide selected columns. The good thing about this shortcut is that you do not need to select entire rows or columns. For example, select B3:D3 then press Ctrl+0 to hide columns B to D. |
