# VBA - array formula functions ok until I use an If THEN either side?

#### Loun

##### New Member
Hello,

I'm sure this is something simple but its been driving me crazy trying to figure it out.

i have the below array formula in VBA. I'm trying to check if the value calculated is above 1..... If I leave off the IF part at one end and the >1 then at the other it prints the value correctly on spreadsheet.

However, when I put the IF part on to try to get it to calculate internally and check the value rather than print the value it is not recognising anything greater than 1.....

any ideas? In sure I'm missing something that states to check the value of the formula or something but I can't figure it out.

If Selection.FormulaArray = "=SUM(IF(CONCATENATE(RC[2],RC[1])=CONCATENATE(RC[2]:R[96]C[2],RC[1]:R[96]C[1]),1,0))" > 1 Then

many thanks

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Hello,

I'm sure this is something simple but its been driving me crazy trying to figure it out.

i have the below array formula in VBA. I'm trying to check if the value calculated is above 1..... If I leave off the IF part at one end and the >1 then at the other it prints the value correctly on spreadsheet.

However, when I put the IF part on to try to get it to calculate internally and check the value rather than print the value it is not recognising anything greater than 1.....

any ideas? In sure I'm missing something that states to check the value of the formula or something but I can't figure it out.

If Selection.FormulaArray = "=SUM(IF(CONCATENATE(RC[2],RC[1])=CONCATENATE(RC[2]:R[96]C[2],RC[1]:R[96]C[1]),1,0))" > 1 Then

many thanks

Maybe
Code:
``If Selection.Value > 1 Then``

Maybe
Code:
``If Selection.Value > 1 Then``

Hi, no that does not work...........

Here is a simplified version of what I am trying to do

Excel 2010
ABC
23DaveJenkins
33DaveJenkins
41Roddavies
53DaveJenkins
61PaulThomas
71DavidEdwards
81PaulRose
91TilerBrown

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
A2{=SUM(IF(CONCATENATE(C2,B2)=CONCATENATE(\$C\$2:\$C\$96,\$B\$2:\$B\$96),1,0))}
A3{=SUM(IF(CONCATENATE(C3,B3)=CONCATENATE(\$C\$2:\$C\$96,\$B\$2:\$B\$96),1,0))}
A4{=SUM(IF(CONCATENATE(C4,B4)=CONCATENATE(\$C\$2:\$C\$96,\$B\$2:\$B\$96),1,0))}
A5{=SUM(IF(CONCATENATE(C5,B5)=CONCATENATE(\$C\$2:\$C\$96,\$B\$2:\$B\$96),1,0))}
A6{=SUM(IF(CONCATENATE(C6,B6)=CONCATENATE(\$C\$2:\$C\$96,\$B\$2:\$B\$96),1,0))}
A7{=SUM(IF(CONCATENATE(C7,B7)=CONCATENATE(\$C\$2:\$C\$96,\$B\$2:\$B\$96),1,0))}
A8{=SUM(IF(CONCATENATE(C8,B8)=CONCATENATE(\$C\$2:\$C\$96,\$B\$2:\$B\$96),1,0))}
A9{=SUM(IF(CONCATENATE(C9,B9)=CONCATENATE(\$C\$2:\$C\$96,\$B\$2:\$B\$96),1,0))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

You can see in column A that the formula correctly enters the number of times the concatenated names appear in the list (These were entered by the macro by taking out the IF etc from the formula that I can't get to work)..............

So I know this part if working correctly.........

However, all I am trying to do is then say IF (The array formula result) > 1 Then (Do this etc)

But when I put the IF part in it just doesn't seem to do anything?

Your suggestion doesn't work I think because I need it to look at the value of the result of the array formula.........

Any other ideas?

Thanks,
Lou

If I haven't made it clear please let me know if someone is able to and I'll try and explain better.

Thanks,

ADVERTISEMENT
First off, your initial formula can be replaced with
=COUNTIFS(\$B\$2:\$B\$96,B2,\$C\$2:\$C\$96,C2)

Now, my real question is Why do we need to do this in VBA?
Why can't we just put the formula in cells. The formulas work right?
I generally believe if you can do it with a formula, then do it with a formula..Not vba.

Jonmo,

Thanks for your response.

To be honest, it's just me trying to get to grips in learning the commands etc in VBA, as I'm just starting out. Not really a case of me using it where I could use formulas.........I'm just learning how to do things so that I get the jist of how it all works.

OK, so even if I used the Countifs formula in VBA and did R1C1 style, it still does not work? What part am I missing that just won't check the result of the formula to see if it's above 1?

So this works but posts the values into column A on the spreadsheet (I don't want it to do this I want to do it internally). I've basically got a loop that will run through each row in column A and check it line by line. (As I say, no real purpose to this at this stage except for my learning!)

ActiveCell.FormulaR1C1 = "=COUNTIFS(RC[1]:R[7]C[1],RC[1],RC[2]:R[7]C[2],RC[2])"

But then when I add on IF at the beginning and >1 at the end it stops working?

If ActiveCell.FormulaR1C1 = "=COUNTIFS(RC[1]:R[7]C[1],RC[1],RC[2]:R[7]C[2],RC[2])" > 1 Then
Msgbox"Duplicate"
Else
Msgbox"Not Duplicate"
End if

Am I missing a stage? If it returns the value when I'm not using the IF etc, why doesn't it register certain rows being > 1 when I add in the IF.

Many Thanks,
Lou

Thanks,
Lou

ADVERTISEMENT
The thing your missing is that this
ActiveCell.FormulaR1C1 = "=COUNTIFS(RC[1]:R[7]C[1],RC[1],RC[2]:R[7]C[2],RC[2])"
does not return a value to VBA. It is only putting a formula in a Cell.

and activecell.formulaR1C1 is not a True/False property that can be tested.
It's an action of entering a formula.

What you can do is put the formula in the cell, then test if the cell > 1

ActiveCell.FormulaR1C1 = "=COUNTIFS(RC[1]:R[7]C[1],RC[1],RC[2]:R[7]C[2],RC[2])"
If ActiveCell.Value > 1 Then
...

What is the ultimate goal you're trying to accomplish?
Are you wanting to Delete the duplicates?
Excel 2010 has a nice Delete Duplicates Tool built right into it.
In the Data Tab.

Sorry, just to add to the above I've got code in place that skips the blank rows etc.

Thanks

Jonmo,

Can you not do an internal calculation using VBA? So that it evaluates something in memory but does not have to put it on the sheet?

Basically I just want to run through each row and if there are any duplicates I am getting a msg box to pop up saying "This is a duplicate" and then formatting the cells so that they can be reviewed afterwards and are clearly marked.........

I thought this would be quite simple to do, but I just can't seem to get it to calculate internally and check against the value, as I don't want any value entered onto the spreadsheet (I'm comfortable in Excel re: duplicates etc, it's just trying to get VBA to work that's my issue )

Thanks

Try

x = Application.Countifs(Range("\$B\$2:\$B\$96"),Cells(ActiveCell.Row,"B"),Range("\$C\$2:\$C\$96"),Cels(ActiveCell.Row,"C"))
If x > 1 Then

Replies
12
Views
171
Replies
1
Views
139
Replies
3
Views
177
Replies
7
Views
104
Replies
8
Views
175

Threads
1,196,384
Messages
6,014,966
Members
441,860
Latest member
Store154

### 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

### 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