CODEWITHSUNDEEP
vbaexcel
J.Rommers
J.Rommers

Reputation: 69

Inserting a formula using vba Excel

I am trying to create a macro that inserts a formula in a specific cell. To do this I'm using the following code.

First I select the proper sheet:

Sheets("Resultaat").Select

Then I select the cell where I want to insert the formula:

Range("C2").Select

I use the ActiveCell.Formula to insert the formula like this:

ActiveCell.Formula = "=COUNTIF(Overview!$G$2:$G$1000;">0")"

This code does not work. It gives me the following error.

compile error: Expected end of statement

I think the error is caused by the ">0" part of the code. Is there any way to make this code work?

Upvotes: 1

Views: 99

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

A few things:

  1. you do not need to activate or select anything but can refer to the cell directly.

  2. Your formula needs to be in American English style when using vba. So use , instead of ;.

  3. You need double quotes to leave single quotes in the formula.

Do this instead:

 Sheets("Resultaat").Range("C2").Formula = "=COUNTIF(Overview!$G$2:$G$1000,"">0"")"

Upvotes: 5

Related Questions