Excel VBA CountIF Runtime 1004 Error 1

[rustychef]

I am trying to dynamically create a cell function on a worksheet by using VBA.

The basic statement from the procedure is:
ActiveCell.FormulaR1C1 = "=COUNTIF('" & wsSOURCE & "'!" & colSource & "2:" & colSource & hLastRow & ",""" & ActiveCell.Offset(0, -1).Value & """)"

wsSOURCE is the name of the worksheet: "Sheet1"
colSource is the source column name
where the data is found: "A"
hLastRow is the last row that contains
data (from "Sheet1"): 104

The statement counts the number of times a word occurs in a column on another worksheet and displays it on a "summary" sheet.

I have tried to manipulate this several different ways and either end up with a runtime error of 1004, or a cell #NAME? reference error.

If I use a debug.print and copy & paste the result in the cells formula window, it works fine, so the logic is good.

When I look at the formula in the worksheet it shows:
=COUNTIF('Sheet1'!'A2':'A104',"x")

where "x" is the value its trying to count.

The problem seems to be with the way
colSource & "2:" & colSource & hLastRow

is being interpreted. Its putting single quotes around the cell references: 'A1':'A104' If I manually remove the single quotes, it works.
(I have also tried the sheet name reference with and without the single quotes, works fine both ways).

There has to be something wrong with the way I am trying to set up the statement.

Can anyone help?
Thanks

Im not having problems with the rest of the procedure, but someone usually wants to look at the code to get the big picture, so here is the code for the procedure.
=========================================================
Sub AI_SummaryPopulate(colStart As Integer, cntUnique As Long, sTitle As String, xlsColNum As Integer, xlsTmpLtr As String, wsSOURCE As String, wsName As String, hLastRow As Long)
Dim colLtrStart As String
Dim colLtrMid As String
Dim colLtrEnd As String
Dim colSource As String
Dim sFormula As String
Dim hTotalsRow As Long

colLtrStart = ChrW((colStart + 64))
colLtrMid = ChrW((colStart + (64 + 1)))
colLtrEnd = ChrW((colStart + (64 + 2)))
colSource = ChrW((xlsColNum + 64))
'Setup AI data
Range(colLtrStart & "1").Select
'Put count total (of unique entries) at top of column
ActiveCell.Value = cntUnique
'Make the font white
Selection.Font.Color = vbWhite
ActiveCell.Offset(1, 0).Select
'Create AI merged column header
ActiveCell.Value = "By " & sTitle
Range(colLtrStart & "2:" & colLtrEnd & "2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.MergeCells = True
End With
'Copy Project data from source worksheet
Sheets(wsSOURCE).Select
Range(xlsTmpLtr & "2:" & xlsTmpLtr & (cntUnique + 1)).Select
Selection.Cut
Sheets(wsName).Select
Range(colLtrStart & "3").Select
ActiveSheet.Paste
Range(colLtrMid & "3").Select

'THESE DONT WORK
'ActiveCell.FormulaR1C1 = "=COUNTIF(" & sFormula & ")"
'sFormula = wsSOURCE & "!" & colSource & "2:" & colSource & hLastRow & "," & colLtrStart & "3"
'ActiveCell.FormulaR1C1 = "=COUNTIF(Sheet1!A2:A104,C3)"
'ActiveCell.FormulaR1C1 = "=COUNTIF(Sheet1!A2" & ChrW(58) & "A104,C3)"

'THESE WORK, but inserts the value only; no formula
'ActiveCell.FormulaR1C1 = Evaluate("=COUNTIF(" & wsSOURCE & "!" & colSource & "2:" & colSource & hLastRow & "," & colLtrStart & "3)")

ActiveCell.FormulaR1C1 = "=COUNTIF('" & wsSOURCE & "'!" & colSource & "2:" & colSource & hLastRow & ",""" & ActiveCell.Offset(0, -1).Value & """)"
Range(colLtrEnd & "3").Select
hTotalsRow = cntUnique + 2
ActiveCell.FormulaR1C1 = "=SUM(" & colLtrMid & "3/$" & colLtrMid & "$" & hTotalsRow & ")"
'Copy formulas
Range(colLtrMid & "3:" & colLtrEnd & "3").Select
Selection.AutoFill Destination:=Range(colLtrMid & "3:" & colLtrEnd & (hTotalsRow - 1)), Type:=xlFillDefault
'Create the Totals line
Range("C" & hTotalsRow).Select
ActiveCell.FormulaR1C1 = "Total in Queue"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(" & colLtrMid & "3:" & colLtrMid & (hTotalsRow - 1) & ")"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(" & colLtrEnd & "3:" & colLtrEnd & (hTotalsRow - 1) & ")"

End Sub 'AI_SummaryPopulate

 

[rustychef]

I have also tried:
Range("D38").FormulaR1C1 = "=COUNTIF('" & wsSOURCE & "'!" & colSource & "2:" & colSource & hLastRow & ",""" & ActiveCell.Offset(0, -1).Value & """)"

and again, the same results.

 

[SkipVought]

hi,

Range("D3:D8").Formula = "=COUNTIF('" & wsSOURCE & "'!" & colSource & "2:" & colSource & hLastRow & ",""" & ActiveCell.Offset(0, -1).Value & """)"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rustychef]

SkipVought

Thanks for the input, it worked great. I hadn't realized there was a difference between "Formula" and "FormulR1C1" since the latter has always worked for me before.

Now I can regrow all the hair I pulled out on this problem and move on.