How do I fix this compile error - Expected end of statement? Inputting a formula into a cell with VB-CodePudding

I've got a variable that adds 3 to a number that a user inputs on a userform. Then, I create a table with that many rows on another sheet. Then, I want a cell on that sheet to count the cells that are in the table and tell the user. I want to do this in VBA because this Sub generates a new sheet/table each time it's run so I can't just enter the formula in the cell and keep it there.

Dim Qnum as Integer 'Defined the integer
Dim ws as Worksheet

Set ws = Sheets("Test")
Qnum = 3   GenTest.txtNum 'adding three to the number of rows the user has requested

with ws
.Range("C1").Formula = "=Counta(D4:D" & Qnum")" 'This gives a compile error - expected end of statement

This is just the part of the code that's returning the error. The rest of the code (not shown) creates a table from the A:D column with the amount of rows that the Qnum variable has.

What am I missing here?

CodePudding user response：

You need an & after Qnum

.Range("C1").Formula = "=Counta(D4:D" & Qnum & ")"