Modified code not working, as expected

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
Try it in the sheet like
=COUNTIF(INDIRECT("'Sheet2'!A:A"),Sheet1!A1)
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Mr2017

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
If your data starts in A2 then the formula should not be looking at A1
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Good spot!!

You were right about the wrong cell reference error!!

Thanks again for your help!!

It's been greatly appreciated!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,113,931
Messages
5,545,087
Members
410,652
Latest member
Zot
Top