VB code for COUNTIF

[Egirl212]

Please help to convert the following COUNTIF formula to VB code..

Logic -

IF

=IF(COUNTIF(G8:G17, "ü")>3, "Medium")

Else IF

=IF(COUNTIF(I8:I17, "ü")>3, "Large"

Then
"Small"

Thank you!

 

[Rick Rothstein]

Please help to convert the following COUNTIF formula to VB code..

Logic -

IF

=IF(COUNTIF(G8:G17, "ü")>3, "Medium")

Else IF

=IF(COUNTIF(I8:I17, "ü")>3, "Large"

Then
"Small"

Below is the equivalent VBA code to what you posted above; however, you did not tell us where the "Small", "Medium" or "Large" text should go, so in my code I simply displayed it in a MessageBox... you will have to change the to output it wherever you actually want it.

[table="width: 500"]
[tr]
	[td]MsgBox Evaluate("IF(COUNTIF(G8:G17,""x"")>3,""Medium"",IF(COUNTIF(I8:I17,""x"")>3,""Large"",""Small""))")[/td]
[/tr]
[/table]

 

[Egirl212]

Thank you Rick!
Cell D19 is where I want to output the results when a criteria matches the above use case.

Below is the equivalent VBA code to what you posted above; however, you did not tell us where the "Small", "Medium" or "Large" text should go, so in my code I simply displayed it in a MessageBox... you will have to change the to output it wherever you actually want it.

[TABLE="width: 500"]
<tbody>[TR]
[TD]MsgBox Evaluate("IF(COUNTIF(G8:G17,""x"")>3,""Medium"",IF(COUNTIF(I8:I17,""x"")>3,""Large"",""Small""))")[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Thank you Rick!
Cell D19 is where I want to output the results when a criteria matches the above use case.

[table="width: 500"]
[tr]
	[td]Range("D19").Value = Evaluate("IF(COUNTIF(G8:G17,""x"")>3,""Medium"",IF(COUNTIF(I8:I17,""x"")>3,""Large"",""Small""))")[/td]
[/tr]
[/table]

 

[Egirl212]

Thank you Rick again for the quick reply.
The code does work but is not returning the accurate value.

E.g. When I have 5 ticks in 'column I' and I expect to output Large but it returns with Medium.
Can I alter the code to say,

Check if G8:G17 is >3, if yes, return Medium. if not then check I8:I17 >3, if yes then output Large, if not then return Small.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Range("D19").Value = Evaluate("IF(COUNTIF(G8:G17,""x"")>3,""Medium"",IF(COUNTIF(I8:I17,""x"")>3,""Large"",""Small""))")[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Thank you Rick again for the quick reply.
The code does work but is not returning the accurate value.

E.g. When I have 5 ticks in 'column I' and I expect to output Large but it returns with Medium.
Can I alter the code to say,

Check if G8:G17 is >3, if yes, return Medium. if not then check I8:I17 >3, if yes then output Large, if not then return Small.

The order of the IF function calls determines the priority of the responses... I used the ordering that you posted figuring that is what you wanted.

Can I alter the code to say,

Check if G8:G17 is >3, if yes, return Medium. if not then check I8:I17 >3, if yes then output Large, if not then return Small.

That is what the code I posted does now. Let me guess at how your users interact with the worksheet and what I think you might want with regard to it. See if this line of code outputs what you want in the order you want given how the user fills in the sheet...

[table="width: 500"]
[tr]
	[td]Range("D19").Value = Evaluate("IF(COUNTIF(I8:I17,""x"")>3,""Large"",IF(COUNTIF(G8:G17,""x"")>3,""Medium"",""Small""))")[/td]
[/tr]
[/table]

 

[Egirl212]

It worked beautifully! Thank you Rick.
Now, how to incorporate values in these 3 columns E8:E17 and G8:G17 and I8:I17 that they are entered only once. That is, in row 8, user can only tick E8, G8 or I8. Same applies through to row 17.

 

[Rick Rothstein]

Now, how to incorporate values in these 3 columns E8:E17 and G8:G17 and I8:I17 that they are entered only once. That is, in row 8, user can only tick E8, G8 or I8. Same applies through to row 17.

Give this event code a try...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim R As Range
  If Not Intersect(Target, Range("E:E,G:G,I:G")) Is Nothing Then
    For Each R In Intersect(Target, Range("E:E,G:G,I:I")).Rows
      If Application.CountA(Intersect(R.EntireRow, Range("E:E,G:G,I:I"))) > 1 Then
        MsgBox "You can only put a value in one cell on Row " & R.Row & " within Columns E, G and I. The value you just placed will be removed."
        Application.Undo
      End If
    Next
  End If
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.