Look in column for specific word then advise count number in Text Box

[ipbr21054]

Hi,
Not sure if title explains correctly but i will try to explain.

I have a userform with the following info for you.

Command Button 1 to run the code.
Text Box 1 through to 10
Worksheet is called MCLIST

When the code is run i would like for a specific word to be found in a specific column then count them up & put value in Text Box.

Example.
Find & count the word HONDA MC KEY #1 in column E then put the value in Text Box 1

If you could advise i will then add more lines following your advice so i then will have code like below.

Find & count the word HONDA MC KEY #1 in column E then put the value in Text Box 1
Find & count the word HONDA MC KEY #2 in column E then put the value in Text Box 2
Find & count the word HONDA REMOTE in column G then put the value in Text Box 5

Would be nice to find last row with values then search towards the top.

Many thanks

 

[ipbr21054]

Just an update.

I have the below working but need advice to remove fixed range & to use xl1up etc

Private Sub UserForm_Initialize()
Set ws = ActiveSheet
Me.TextBox1 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$103"), "HONDA MC KEY #1")
Me.TextBox2 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$103"), "HONDA MC KEY #2")
Me.TextBox3 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$103"), "HONDA MC KEY #3")
Me.TextBox4 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$103"), "HONDA MC KEY #4")
Me.TextBox5 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$103"), "REMOTE FOB 3B")
Me.TextBox6 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$103"), "YAMAHA YH35")
Me.TextBox7 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$103"), "BUNDLE")
Me.TextBox8 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$103"), "BLACK")
Me.TextBox9 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$103"), "GREY")
Me.TextBox10 = Application.WorksheetFunction.CountIf(ws.Range("$A$8:$K$103"), "RED")
End Sub