Need VBA Help – How to Insert an Excel Function into B2 of an 8 column table with VBA

[wddogger]

Need VBA Help – How to Insert an Excel Function into B2 of an 8 column table with VBA

The purpose of the formula (function) to be inserted into B2 of Table1 is twofold; convert Date value in (Table1 / cell A2) to text and concatenate to it the Description value (Table1 / cell C2) . The formula is to be placed in Table1 / cell B2. The vba statements I am trying to use are as follows:

Sub Insert_Formula()
Range.formula = “=CONCATENATE(TEXT([@Date],"mm/dd/yy"),"_",{@Description])”
End Sub​

The above statements cause a Compile Error – Expected End of Statement with mm highlighted.

If I convert Table1 to a range and insert the formula =CONCATENATE(TEXT(A2,"mm/dd/yy"),"_",C2) into cell B2 of the worksheet, the formula performs the date to text conversion & concatenation correctly.

I presume I am using incorrectly structured references for the range.formula = statement but have not been able to figure it out. Any help would be greatly appreciated.

 

[JLGWhiz]

Did you try

Range("B2").Formula = “=CONCATENATE(TEXT([@Date],"mm/dd/yy"),"_",{@Description])”

 

[wddogger]

Did you try

Range("B2").Formula = “=CONCATENATE(TEXT([@Date],"mm/dd/yy"),"_",{@Description])”

Color me "dense"! & slow learner. Thanks JLGWhiz

 

[JLGWhiz]

We have all been there.
Regards, JLG