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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
The formula is not valid, you have more ( than you have )
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks for the response, Fluff.

I'm not sure what you meant by your answer, though?

Did you mean more inverted commas?
 
Last edited:

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
No, more open parentheses than closed parentheses.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The best way to check a formula is to put it into a cell first, that way you can get the correct formula.
 

Mr2017

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Must admit looking back at my post it is not very clear. Sorry for that.

Have you now got it sorted?
 

Mr2017

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
The extra bracket does not go at the end. Did you try writing the formula in a cell?
 

Mr2017

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

Watch MrExcel Video

Forum statistics

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