VBA > Cell Formula dependent on another Cell Value

[phor89]

Hi All!

HNY to everyone. My first time posting on this forum so apologies if any of my etiquette is off.

Anyway, I am trying to write some code where the value in a cell (B1) changes dependent on the value in a different cell (A1). I've built some experimental code where if A1 = "yes" then the formula in B1 = Average(C1:C5):

Sub Addformula()
If Range("$A$1") = "yes" Then
Worksheets("Sheet1").Range("B1").Formula = "=Average(C1:C5)"
Else
Worksheets("Sheet1").Range("B1").Formula = ""
End If
End Sub

This works fine. However, when I am trying to add a non-native formula (not just a simple Excel average but one that pulls data from an external addin) it doesn't work. For those interested, it is a formula used to pull data from Thomson Reuters, and its pretty long (see below). The result is just a compile error, which I don't know how to diagnose because I am relatively new to VBA.

Any help would be greatly appreciated!

***External formula in case it matters:

=TR("SCREEN(U(IN(Equity(active,public,primary))/*UNV:Public*/), IN(TR.ExchangeCountryCode,""US""), IN(TR.HQCountryCode,""AI"",""AG"",""AR"",""AW"",""BS"",""BB"",""BZ"",""BO"",""BR"",""KY"",""CL"",""CO"",""CR"",""CW"",""DO"",""EC"",""SV"",""FK"",""G"&"T"",""GY"",""HN"",""JM"",""MX"",""NI"",""PA"",""PY"",""PE"",""PR"",""KN"",""LC"",""VC"",""SX"",""SR"",""TT"",""TC"",""UY"",""VE"",""VG"",""VI"",""BM"",""CA"",""GL"",""US""), TR.CompanyMarketCap>1000000000, CURN=USD)","TR.ExchangeTicker"&";TR.ExchangeName;TR.CommonName;TR.NAICSSector;TR.NAICSSectorCode;TR.NAICSSubsector;TR.NAICSSubsectorCode;TR.NAICSInternationalIndustry;"&"TR.CompanyFYearEnd;TR.MarketCapLocalCurn;TR.PreferredMeasureActValue(Period=FY0,SDate=0D)/*EPS IBES Actual (Last Fisc YR)*/;TR.PreferredMeasureMeanEst(Period=FY1,SDate=0D)/*EPS - Mean (Curr Fisc YR)*/;TR.PreferredMeasure"&"MeanEst(Period=FY2,SDate=0D)/*EPS - Mean (Next Fisc YR)*/;TR.PreferredMeasureMeanEst(Period=FY3,SDate=0D)/*EPS - Mean (FY3)*/;"&"(TR.PreferredMeasureMeanEst(Period=FY1,Sdate=0D)-TR.PreferredMeasureActValue(Period=FY0,Sdate=0D))/ABS(TR.PreferredMeasureActValue(Period=FY0,Sdate=0D));"&"(TR.PreferredMeasureMeanEst(Period=FY2,Sdate=0D)-TR.PreferredMeasureMeanEst(Period=FY1,Sdate=0D))/ABS(TR.PreferredMeasureMeanEst(Period=FY1,Sdate=0D));"&"(TR.PreferredMeasureMeanEst(Period=FY3,Sdate=0D)-TR.PreferredMeasureMeanEst(Period=FY2,Sdate=0D))/ABS(TR.PreferredMeasureMeanEst(Period=FY2,Sdate=0D));"&"TR.PriceClose(SDate=0D,Curn=USD)/TR.PreferredMeasureMeanEst(Period=FY1,Sdate=0D,Curn=USD);"&"TR.PriceClose(SDate=0D,Curn=USD)/TR.PreferredMeasureMeanEst(Period=FY2,Sdate=0D,Curn=USD);"&"TR.PriceClose(SDate=0D,Curn=USD)/TR.PreferredMeasureMeanEst(Period=FY3,Sdate=0D,Curn=USD);"&"(TR.PriceClose(SDate=0D,Curn=USD)/TR.PreferredMeasureMeanEst(Period=FY1,Sdate=0D,Curn=USD))/((TR.PreferredMeasureMeanEst(Period=FY1,Sdate=0D)-TR.PreferredMeasureActValue(Period=FY0,Sdate=0D))/ABS(TR.PreferredMeasureActValue(Period=FY0,Sdate=0D))*100);"&"(TR.PriceClose(SDate=0D,Curn=USD)/TR.PreferredMeasureMeanEst(Period=FY2,Sdate=0D,Curn=USD))/((TR.PreferredMeasureMeanEst(Period=FY2,Sdate=0D)-TR.PreferredMeasureMeanEst(Period=FY1,Sdate=0D))/ABS(TR.PreferredMeasureMeanEst(Period=FY1,Sdate=0D))*100);"&"(TR.PriceClose(SDate=0D,Curn=USD)/TR.PreferredMeasureMeanEst(Period=FY3,Sdate=0D,Curn=USD))/((TR.PreferredMeasureMeanEst(Period=FY3,Sdate=0D)-TR.PreferredMeasureMeanEst(Period=FY2,Sdate=0D))/ABS(TR.PreferredMeasureMeanEst(Period=FY2,Sdate=0D))*100)"&";TR.TtlDebtToTtlEquityPct;"&"TR.ShortInterestDTC(SDate=0D);"&"TR.EPSActValue(Sdate=0D,Period=LTM)/TR.RevenueActValue(Sdate=0D,Period=LTM)*TR.CompanySharesOutstanding(SDate=0D)/*Net Profit Margin*/;TR.DividendYield/100;"&"TR.FwdEVToEBITDA;TR.FCFMeanYield/100;TR.FwdNetDebtToEBITDA;(TR.EPSActValue(Period=LTM,SDate=0D)/(((TR.CommShareholdersEqty(Period=FQ0,SDate=0D)+TR.CommShareholdersEqty(Period=FQ-3,SDate=0D))/2)/TR.CompanySharesOutstanding(SDate=0D))+TR.EPSActVa"&"lue(Period=LTM,SDate=0D-1AY)/(((TR.CommShareholdersEqty(Period=FQ0,SDate=0D-1AY)+TR.CommShareholdersEqty(Period=FQ-3,SDate=0D-1AY))/2)/TR.CompanySharesOutstanding(SDate=0D-1AY))+TR.EPSActValue(Period=LTM,SDate=0D-2AY)/(((TR.CommShareholdersEqty(Perio"&"d=FQ0,SDate=0D-2AY)+TR.CommShareholdersEqty(Period=FQ-3,SDate=0D-2AY))/2)/TR.CompanySharesOutstanding(SDate=0D-2AY))+TR.EPSActValue(Period=LTM,SDate=0D-3AY)/(((TR.CommShareholdersEqty(Period=FQ0,SDate=0D-3AY)+TR.CommShareholdersEqty(Period=FQ-3,SDate"&"=0D-3AY))/2)/TR.CompanySharesOutstanding(SDate=0D-3AY))+TR.EPSActValue(Period=LTM,SDate=0D-4AY)/(((TR.CommShareholdersEqty(Period=FQ0,SDate=0D-4AY)+TR.CommShareholdersEqty(Period=FQ-3,SDate=0D-4AY))/2)/TR.CompanySharesOutstanding(SDate=0D-4AY)))*20/*"&"ROE 5 YR Avg*//*ROE 5 YR Avg*/;TR.PreferredMeasureActSurprise(Period=FQ-3,Sdate=0D);"&"TR.PreferredMeasureActSurprise(Period=FQ-2,Sdate=0D);"&"TR.PreferredMeasureActSurprise(Period=FQ-1,Sdate=0D);"&"TR.PreferredMeasureActSurprise(Period=FQ0,Sdate=0D);"&"TR.MeanPctChg(Period=FY1,WP=60D);"&"TR.MeanPctChg(Period=FY2,WP=60D);TR.ExpectedReportDate(Period=FQ1)"&"/*Next EPS Report Date*//*Next EPS Report Date*/","curn=USD RH=IN",$A$2)

 

[phor89]

I feel like I should clarify - I am not asking for help on the TR formula. I know it is correct since I am using it in a cell directly. But I want to be able to make changes to that formula by referencing a different cell as in the example above. For some reason, I can't use the TR formula in VBA but I can if it is directly inputted in a cell in a worksheet.

Again, help would be much appreciated.