Modified code not working, as expected

[Mr2017]

Hi

I added the indirect function to a formula in VBA, but the macro stops at the 'activecell.formula' line below.

I suspect that it's because the VBA editor doesn't like one of the inverted commas?

Can anyone spot the issue here, please?

The sample data this runs on is below:

Sheet 1 has two columns, with data starting in A1, as per below:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Number[/TD]
[TD="width: 64"]Count[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 has one column, with data starting in A1, as per below.

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
The macro should insert a count if into cell B2 of Sheet 1, then drag it down.

TIA

Sub Macro2()'Populate Data


Range("b2").Select
ActiveCell.Formula = "=COUNTIF(INDIRECT(""'Sheet2'!A:A"",'Sheet1'!A1)"


Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=COUNTIF(INDIRECT(""'Sheet2'!A:A"",'Sheet1'!A1)"


End Sub

 

[Fluff]

The formula is not valid, you have more ( than you have )

 

[Mr2017]

Thanks for the response, Fluff.

I'm not sure what you meant by your answer, though?

Did you mean more inverted commas?

 

[Scott Huish]

No, more open parentheses than closed parentheses.

 

[Fluff]

The best way to check a formula is to put it into a cell first, that way you can get the correct formula.

 

[Mr2017]

I'd originally read your post in "normal" rather than "VBA" mode way, so I was expecting a word after the word "more" and was also expecting a word after the word "have," so that's the reason I didn't understand it, at first. But thank you for pointing the error out. It's appreciated.

 

[Fluff]

Must admit looking back at my post it is not very clear. Sorry for that.

Have you now got it sorted?

 

[Mr2017]

Not yet - I added in a closed bracket at the end ie ")" as you correctly pointed out, but the formula is still giving me an error?

 

[Fluff]

The extra bracket does not go at the end. Did you try writing the formula in a cell?

 

[Mr2017]

Yes - this is what I tested in the cell.

=COUNTIF(INDIRECT(""'Sheet2'!A:A"",'Sheet1'!A1))

In the VBA code, I'll ensure that the additional closed bracket ie ")" is put before the last quotation mark.

Not sure why the formula is not working, though? Apologies - this is only the second time that I'm using the indirect formula - it's not one that I'm familiar with....

Any further insight would be greatly appreciated.

Thanks in advance.