Modified code not working, as expected

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
644
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

NumberCount
12
21
30

<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


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
 
Try it in the sheet like
=COUNTIF(INDIRECT("'Sheet2'!A:A"),Sheet1!A1)
 
Upvote 0

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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).

Number
1
2
3

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

And Sheet 2 has the values 1, 1, and 2 in cells A1, A2, and A3, respeticlvey (please see below)

1
1
2

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

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)
 
Upvote 0
If your data starts in A2 then the formula should not be looking at A1
 
Upvote 0
Good spot!!

You were right about the wrong cell reference error!!

Thanks again for your help!!

It's been greatly appreciated!!
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top