Formula with "String" & Reference to Other Tab

[rschmidt46]

I'm trying to get the syntax correct for a formula entered with Excel VBA. The actual formula in the cell should be as follows:

="Average for days able to walk from " & TEXT('New Schedule 2022-08 to 2022-09'!C4,"mm/dd/yy") & " to " & TEXT('New Schedule 2022-08 to 2022-09'!C31,"mm/dd/yy")

This formula is correct and yields the proper information ("Average for days able to walk from 08/10/22 to 09/03/22"). The data comes from two cells on a tab called "New Schedule 2022-08 to 2022-09". When I add a new tab to the spreadsheet, I want VBA to enter a new formula to calculate a new average, which includes data from the new tab. There are additional tabs with similar names as part of tracking an ongoing workout routine. The full set of them is copied below.

I am having trouble coding the formula in VBA (Microsoft Excel 2010 [yes, it's an old version!]. The incorporation of both double quotes for the text portion of the formula and single quotes to reference data on a different tab has me completely stumped. I hope some helpful user out there can guide me into the proper syntax. I have tried for many hours to figure it out myself and researched it on the web but am still doing something wrong.

63.83​	Average for days able to walk from 08/10/22 to 09/03/22
70.44​	Average for days able to walk from 09/04/22 to 10/01/22
46.98​	Average for days able to walk from 10/02/22 to 10/29/22
72.60​	Average for days able to walk from 10/30/22 to 11/26/22
58.16​	Average for days able to walk from 11/27/22 to 12/24/22
86.95​	Average for days able to walk from 12/25/22 to 01/21/23
81.69​	Average for days able to walk from 01/22/23 to 02/18/23
66.44​	Average for days able to walk from 02/19/23 to 03/18/23
71.48​	Average for days able to walk from 03/19/23 to 04/15/23
62.00​	Average for days able to walk from 04/16/23 to 05/13/23

 

[offthelip]

I find the real problem is that the double quote symbol, is the symbol which is use to delineate the start and end to the string quotes, to get round this I define a variable which is ascii character 34 which happens to be the double quote character and then I use this variable whenever I need double quotes in the final string, So then I know all the double quotes in my code are the start or end of a string and won't ( or shouldn't) appear in the formula. So try this:

Sub test()
'="Average for days able to walk from " & TEXT('New Schedule 2022-08 to 2022-09'!C4,"mm/dd/yy") & " to " & TEXT('New Schedule 2022-08 to 2022-09'!C31,"mm/dd/yy")
'="Average for days able to walk from "
'& TEXT('New Schedule 2022-08 to 2022-09'!C4,"mm/dd/yy") & " to " &
'TEXT('New Schedule 2022-08 to 2022-09'!C31,"mm/dd/yy")
tt = Chr(34)
tabname = "'New Schedule 2022-08 to 2022-09'"
formul = "=" & tt & "Average for days able to walk from " & tt & " & TEXT(" & tabname & "!C4," & tt & "mm/dd/yy" & tt & ") & " & tt & " to " & tt & " & TEXT(" & tabname & "!C31," & tt & "mm/dd/yy" & tt & ")"
Range("d2") = formul
End Sub

 

[rschmidt46]

Thank you very much! It works exactly as it's supposed to.