VBA Counter that sets itself to the number a specifc word appears in a sheet when excel is opened.

[FelixExcel]

Hey all, Im currently working on an excel project in which I use a counter. Right now I use following code to reset the counter to 0 everytime excel is opened.

Private Sub UserForm_Initialize()

counter = 0

End Sub

I now noticed that this is actually not what I need. I would need the counter to set itself to the number a specific word, for example "hello" appears in my excel sheet. I have a feeling there is an easy solution to this, which shouldnt involve to many lines of code.

So far I made a bit of progress and found out that I can use the formular =SUMIF(range, "Text") to count how often a certain word appears in a certain range, but Im not sure how Im able to integrate this number into my vba code. Is there an option to just set my counter to the value of the cell that has the sumif formular. For example if the formular would be in the cell A2:

Private Sub UserForm_Initialize()

counter = A2

End Sub

I would appreciate any help with this.

 

[Snakehips]

@FelixExcel If you have the desired count value in a specific cell then code will be along these lines...

counter = ThisWorkbook.Sheets("Edit To Suit").Range("A2")

Hope that helps.

 

[FelixExcel]

@FelixExcel If you have the desired count value in a specific cell then code will be along these lines...

counter = ThisWorkbook.Sheets("Edit To Suit").Range("A2")

Hope that helps.

Thanks for the help snakeships. It worked!

 

[FelixExcel]

Hey all, Im currently working on an excel project in which I use a counter. Right now I use following code to reset the counter to 0 everytime excel is opened.

Private Sub UserForm_Initialize()

counter = 0

End Sub

I now noticed that this is actually not what I need. I would need the counter to set itself to the number a specific word, for example "hello" appears in my excel sheet. I have a feeling there is an easy solution to this, which shouldnt involve to many lines of code.

So far I made a bit of progress and found out that I can use the formular =SUMIF(range, "Text") to count how often a certain word appears in a certain range, but Im not sure how Im able to integrate this number into my vba code. Is there an option to just set my counter to the value of the cell that has the sumif formular. For example if the formular would be in the cell A2:

Private Sub UserForm_Initialize()

counter = A2

End Sub

I would appreciate any help with this.

Meant to say COUNTIF instead of SUMIF

 

[Snakehips]

Meant to say COUNTIF instead of SUMIF

That will make no difference to the code.