Passing Texbox string array to countif formula in Excel

VBvirg

New Member
Joined
Jul 2, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Trying to get an Array to work with Formula Countif, but cant manage to get the countif right, can anyone give me a pointer please? I have a userform with 5 textboxes on it add a search word in 1 or maybe 2 i wanted them to use the countif formula.

Sorry if this seems simple to you guys im still a newbie.

Worksheets("Questions").Activate
Dim WordArray As Variant
WordArray = Array(UsrFormSearch.TxtSearch1.Value, UsrFormSearch.TxtSearch2.Value,
UsrFormSearch.TxtSearch3.Value, UsrFormSearch.TxtSearch4.Value, UsrFormSearch.TxtSearch5.Value)

ActiveSheet.Range("F2").Formula = "=CountIfs(B2:E2, {WordArray})"

LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row

Range("F2: " & "F" & LastPopulatedRow).FillDown
 

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.
Welcome to the forum.

You need a string, not an array, and you'll need to wrap the COUNTIF in a SUM formula too:

Code:
Dim WordArray As Variant
WordArray = chr(34) & UsrFormSearch.TxtSearch1.Value & chr(34) & "," & chr(34) & UsrFormSearch.TxtSearch2.Value & chr(34) & "," & chr(34) & 
UsrFormSearch.TxtSearch3.Value & chr(34) & "," & chr(34) & UsrFormSearch.TxtSearch4.Value & chr(34) & "," & chr(34) & UsrFormSearch.TxtSearch5.Value & chr(34)

ActiveSheet.Range("F2").Formula = "=SUM(CountIf(B2:E2, {" & WordArray & "}))"
 
Upvote 0
Hey RoryA, thanks for your response, your code puts the following formula in cell F2 but not does not take the value from usrformsearch.textsearch1.value , any thoughts?

=COUNTIFS(B3:E3, usrformsearch.txtsearch1.value)
 
Upvote 0
You're not running my code if that's the formula you end up with.
 
Upvote 0
Rory, please accept my humble apologies, the ERROR was between the Keyboard and the Chair ie me. Thank you so much for your help, you are a legend.
 
Upvote 0
No worries. Glad to help. :)
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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