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:

Number	Count
1	2
2	1
3	0

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

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

1

1

2

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

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.