excel macro variable as a formula

[mcrandall99]

i have a excel vba macro in which I have a complicated formula, which I have managed to put together using various variables. I have combined them and I can see the formula in msgbox... but I can't paste it into excel sheet - it doesn't show a formula.

could someone tell me where I am wrong with this?

Sub dated()
Dim TheDate As Date
Dim msg As String
Dim string1 As String
Dim string2 As String
Dim string3 As String
Dim strong4 As String
Dim string5 As String
Dim string6 As String
Dim string7 As String
Dim string8 As String
Dim string9 As String
Dim string10 As String
Dim strig11 As String
Dim string12 As String
Dim combined As String
MyDate = "2/02/2019"
msg = DateDiff("d", MyDate, Now)
string1 = "GETSATDAT"
string2 = "("""
string3 = "\\modbusdataserver.epic.systeminternalsK.com\KNN_SIFT_NEWYORK:TESTMAT:DRIVE Linx:KNN_SIFT_NEWYORK:TESTMAT_01_NNN._09_GOR_0221.IV"
string4 = ","
string5 = " "" "
string6 = "*"
string7 = "-"
string8 = "d"
string9 = 57
string10 = "inside"
string11 = ")"
string12 = "="
combined = string12 & string1 & string2 & string3 & Chr(34) & string4 & string5 & string6
combined2 = string7 & msg & string8 & Chr(34) & string4 & string6 & string4 & string9 & Chr(34)
combined3 = Chr(34) & string4 & Chr(34) & string10 & Chr(34) & string11
combined4 = combined & combined2 & combined3
MsgBox combined4
Range("A1").Select
ActiveCell.FormulaR1C1 = "=combined4"
Range("A2").Select
End Sub

 

[Marc L]

​

As your formula can be directly written to the worksheet without any 'string' variable​

​

but anyway according to your VBA procedure the correct line is ActiveCell.Formula = combined4 …​

 

[mcrandall99]

​

As your formula can be directly written to the worksheet without any 'string' variable​

​

but anyway according to your VBA procedure the correct line is ActiveCell.Formula = combined4 …​

you could figure out why I went through the hassle because I don't know much

=GETSATDat("\\modbusdataserver.epic.systeminternalsK.com\KNN_SIFT_NEWYORK:TESTMAT:DRIVE Linx:KNN_SIFT_NEWYORK:TESTMAT_01_NNN._09_GOR_0221.IV","*-159x","*",225,"","frside")

the above line is the actual formula - but I want to pass on variables to where '159' is placed above. basically the variable msg will replace "159" - rest of the formula will stay as is. is there an easier way to accomplish this?

 

[Marc L]

According to your actual formula a VBA demonstration as a beginner starter :​

Sub Demo1()
    Const F = "=GETSATDat(""\\modbusdataserver.epic.systeminternalsK.com\KNN_SIFT_NEWYORK:TESTMAT:DRIVE Linx:KNN_SIFT_NEWYORK:TESTMAT_01_NNN._09_GOR_0221.IV"",""*-¤x"",""*"",225,"""",""frside"")"
    [A1].Formula = Replace(F, "¤", DateDiff("d", #2/2/2019#, Now))
End Sub