0

So, I wrote this code as a relative beginner (Much help from here.). I take a weekly sheet and import it as a new worksheet. I then compile the data on that worksheet to eventually chart everything.

I am now rewriting the code to use a single master data table on a summary data tab to pull the data from the latest worksheet. I want to plug in the formulas in my VBA into the cells in the master table. I want to use a variable which i have assigned the latest sheet name to, inside my formula, so that I can use the same VBA script for each new line added, so that the new sheet name gets passed to the variable instead of extensive lines of clunky code.

I understand the difference between how I have to write formulas in VBA versus directly in Excel, so, I am hoping this is a syntax error I am causing. I also understand quotes imply "Write as text" but I don't know how to remove the quotes so the formulas get plugged in but still place the sheet name into the formula instead of the variable the sheet name is stored in: See below:

'Add all formulas across for AT department to pull the appropriate data from the new tab to the master table. 
.Range("F" & DstLstRw3).Formula = "=IFERROR(COUNTIFS([wsDst1]C[-4],""AT"",[wsDst1]C,1),0)" 
.Range("G" & DstLstRw3).Formula = "=IFERROR(COUNTIFS(wsDst1!C[-5],""AT"",wsDst1!C,0),0)" .Range("H" & DstLstRw3).Formula = "=IFERROR(((RC[-2]+RC[-1])-RC[-1])/(RC[-2]+RC[-1]),0)"

When the VBA goes across plugging the formulas into the cells, rather than inserting the sheet name, it is actually inserting the text variable name where the sheet name is stored. How do I get the sheet name to pass into the formulas in place of the variable name, despite the quotes?

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
iQuikDraw
  • 1
  • 2
  • 1
    Code should be text not a photo. Please [edit] the post to include the code as text. – Scott Craner Aug 20 '21 at 16:14
  • based on the link you will need to use something like: `.Range("F" & DstLstRw3).Formula = "=IFERROR(COUNTIFS('" & wsDst1.Name & "'!C[-4],""AT"",'" & wsDst1.Name & "'C,1),0)"` This assumes that `wsDst1` is a worksheet variable. If it is a String variable then remove the `.Name`s – Scott Craner Aug 20 '21 at 16:52
  • Thank you, Sir! That did it, other than it failed until I added the exclamation point after the second spot where the variable is plugging in the ws name just before the "C"...Otherwise it worked great. I appreciate that so much .Range("F" & DstLstRw3).Formula = "=IFERROR(COUNTIFS('" & wsDst1.Name & "'!C[-4],""*AT*"",'" & wsDst1.Name & "'!C,1),0)" – iQuikDraw Aug 20 '21 at 17:22

0 Answers0