Passing Texbox string array to countif formula in Excel

[VBvirg]

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

 

[RoryA]

Welcome to the forum.

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

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 & "}))"

 

[VBvirg]

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)

 

[RoryA]

You're not running my code if that's the formula you end up with.

 

[VBvirg]

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.

 

[RoryA]

No worries. Glad to help.