CODEWITHSUNDEEP
vbaexcelexcel-formula
acenturyandabit
acenturyandabit

Reputation: 1418

VBA Set Cell value to text containing ' not working

I'm trying to get a macro to type out a heap of formulae for me. The formula goes something like this:

=COUNTIF('other_sheet'!A:A,"hello*")

The user can specify A and other_sheet, so I select the cell and then go

ActiveCell.Value = "=COUNTIF('" & othercell & "'!" & column & ":" & _
    column & """,hello*"")"

But it keeps giving me errors like:

1004: Object Defined Error

I have tried using ActiveCell.Text, ActiveCell.Formula etc. and they all don't work.

Upvotes: 1

Views: 521

Answers (2)

acenturyandabit
acenturyandabit

Reputation: 1418

Apparently you can only put proper, completed answers into a cell.

i.e. if the output turns out to be "=sum(A:A" and a bracket isn't closed, then the application throws an error just as if you typed it in manually.

Upvotes: 0

Dan Donoghue
Dan Donoghue

Reputation: 6216

It needs to be ActiveCell.Formula and ", is the wrong way around next to Hello. It should be:

ActiveCell.Formula= "=COUNTIF('" & othercell & "'!" & column _
    & ":" & column & ",""hello*"")"

Upvotes: 1

Related Questions