Trying to Remove Single Quotes from Macro Outputs using VBA.

[Tauben]

I am currently trying to use the COUNTA function in Excel 13 to find the number of cells populated with data. When I execute my code it is supposed to insert the formula into the selected cell that would refer to other cells for the counting process. When it does this I am asked to open a file to find the sheet that it refers to even though it is in the same workbook.

Here is my code.

Private Sub CommandButton1_Click()

Dim rangeB As String

rangeB = "B10:B100"

Sheets("Form").Select ' Used because VBA would not select the cell without it being mentioned 2 times
Sheets("Form").Range("G13").Select
ActiveCell.Formula = _
"=COUNTA(Summary6-10!" & rangeB & ")"

End Sub

When I look at the cell G13 the formula is:
=COUNTA(Summary6-'10'!B10:B100)

The formula that I am trying to get is:
=COUNTA(Summary6-10!B10:B100)

I have simplified my code to see if I could eliminate the error but even with this code the single quotes still appear in the formula. Is my syntax wrong or is there something else that I am missing?

Any help would be greatly appreciated.
Thanks,
Garrett

 

[steve the fish]

Try this:

Dim rangeB As String

rangeB = "B10:B100"

Sheets("Form").Range("G13").Formula = "=COUNTA('Summary6-10'!" & rangeB & ")"

 

[Tauben]

Try this:

Dim rangeB As String

rangeB = "B10:B100"

Sheets("Form").Range("G13").Formula = "=COUNTA('Summary6-10'!" & rangeB & ")"

Thanks! That works great. Could you possibly explain what changed?

Garrett