Inserting Formula into cells VBA

Question

I am trying to "print" formulas into an excel sheet with VBA. The code works without the "=" sign in front of the COUNTIF, but when I Insert it I get a Run-Time error 1004.

Can someone help me?

Here is the code that gets the error

Sub Looksie()

    Dim Tru As Range
    Dim i As Integer
    i = 2
    
    For Each Tru In Range("A2:A300")
        
            Sheets("Resultat").Select
            Cells(i, 2).Formula = "=COUNTIF(Input!" & Cells(19, i + 1).Address & ":" & Cells(5000, i + 1).Address & ";" & """" & "A" & """" & ")"
     
            i = i + 1
        
    Next Tru
 End Sub

Thank you

Try replacing `";"` with `","` (list separator in US version). If you're on an international version, then `";"` may be correct; in that case, make sure that you actually have a sheet called `Input`. You're getting the error, because Excel does not allow you to assign incorrect formulas. — ouroboros1, May 02 '22 at 08:12
Also, you want to get rid of `Sheets("Resultat").Select` and instead use `Sheets("Resultat").Activate` *before* the loop. — ouroboros1, May 02 '22 at 08:18
@ouroboros1: In VBA, you need to use comma as separator independent of the regional settings when writing a formula (very often asked and answered here on SO). And please don't give the advice for using `Activate` (or `Select`), just always qualify the Range. See https://stackoverflow.com/a/10717999/7599798 — FunThomas, May 02 '22 at 08:35

score 0 · Accepted Answer · answered May 02 '22 at 08:24

Could you use next code?

I'm edited only ":" to ",".

Sub Looksie()

    Dim Tru As Range
    Dim i As Integer
    i = 2
    
    For Each Tru In Range("A2:A300")
        
            Sheets("Resultat").Select
            Cells(i, 2).Formula = "=COUNTIF(Input!" & Cells(19, i + 1).Address & ":" & Cells(5000, i + 1).Address & ",""A"")"
     
            i = i + 1
        
    Next Tru
 End Sub

Inserting Formula into cells VBA

1 Answers1