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]

Try it in the sheet like
=COUNTIF(INDIRECT("'Sheet2'!A:A"),Sheet1!A1)

 

[Mr2017]

Thanks - the error I was getting when trying to add the formula to the cell has disappeared, so I'm grateful for your amendment.

However, it didn't give me the result that I expected?

Sheet 1 has the values 1, 2, and 3 in cells A2, A3 and A4, respectively (please see below).

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

And Sheet 2 has the values 1, 1, and 2 in cells A1, A2, and A3, respeticlvey (please see 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]

So I would have expected this formula (when inserted into cell B2 in Sheet 1 and dragged down to B4) to return the values 2, 1, and 0 in cells B2, B3 and B4.

However, it returned 0, 2, and 1, respectively.

Do you know what may need to be amended?

TIA

I'm glad the previous error has gone, with your help!
=COUNTIF(INDIRECT("'Sheet2'!A:A"),Sheet1!A1)

 

[Fluff]

If your data starts in A2 then the formula should not be looking at A1

 

[Mr2017]

Good spot!!

You were right about the wrong cell reference error!!

Thanks again for your help!!

It's been greatly appreciated!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Mr2017]

You're welcome.