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:
<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.
<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
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>
TIA
Code:
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