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

Loun

New Member
Joined
Dec 29, 2012
Messages
28
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
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
 
Upvote 0
If I haven't made it clear please let me know if someone is able to and I'll try and explain better.

Thanks,
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Sorry, just to add to the above I've got code in place that skips the blank rows etc.

Thanks
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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