VBA ActiveCell set to Concatenated String-CodePudding

Long time reader first time asker..
I have been at wits end with setting an ActiveCell value to be a string type variable. I'm sure it is something simple however I just cannot figure out why the error is..
This code is running from my personal xlsb.
Any help is appreciated and thank you in advanced.

The error I get is runtime 1004 Application-defined or object-defined error. I am open to solutions, however I am more interested in why it doesn't work.

Public Sub gogogo()

Dim X As Integer
Dim Y, str1, str2, str3 As String

X = 1

Do

    Y = CStr(X)
    str1 = "=COUNTIF('Failure rate (Car)'!$B26:$BBB26,'="
    str2 = "')"
    str3 = str1 & Y & str2  'Successfully makes sentence'
    Cells(8, X   14).Value = str3 'Dies on this line Error 1004'

    X = X   1
    Loop Until X = 52

End Sub

CodePudding user response：

Not an answer, but too long for comments.

Dim X As Integer  
Dim Y, str1, str2, str3 As String

X should be a Long
Your second Dim defines Y, str1 and str2 as Variant and str3 as String.
Correct syntax would be:

Dim Y as string, str1 as string, str2 as string, str3 As String

For your formula, try

str3 = "=COUNTIF('Failure rate (Car)'!$B26:$BBB26, " & Y & ")"

And of course, like @Spencer Barnes mentioned, use .Formula instead of .Value

CodePudding user response：

str3 is not going to be the cell value, it's the cell formula:
Cells(8, X 14).Formula = str3

If you want the cell to actually show the full 'sentence' rather than the result of the formula, put a single quote ' at the start (won't show in the cell).