How to use Functions in VBA with my current code

[Akw47]

I am new to VBA. Firstly, the function that I have is :

Function SumNums(pWorkRng As Range, Optional xDelim As String = " ") As Double
Dim arr As Variant
Dim xIndex As Long
arr = Split(pWorkRng, xDelim)
For xIndex = LBound(arr) To UBound(arr) Step 1
SumNums = SumNums + VBA.Val(arr(xIndex))
Next
End Function

Using this, from a cell (etc. F9) filled with characters, 1 White & 2 Red, excel can read 3. However, I need to connect with my other function which based on the value calculated on the same cell (F9), it does the number of times of clicks.

Private Sub CommandButton1_Click()
With Me.CommandButton1
.Tag = Val(.Tag) + 1
If .Tag = SumNums.Cells(9, 6).Value Then
rows(9).Interior.ColorIndex = 10
.Tag = 0
End If
End With
End Sub

But the code ^ there don't work. I also want the row that excel reads to be automatically colour index = 10. Instead of me specifying rows(9).
Thankss.

 

[Fluff]

I understand that part, but how do you determine which cell/row to look at?

 

[Akw47]

I understand that part, but how do you determine which cell/row to look at?

I thought of making it based on the specific number : row, and the specific column : bags (which will be the same throughout)

 

[Akw47]

I thought of making it based on the specific number : row, and the specific column : bags (which will be the same throughout)

so if my specific number is A1, it will register row(1). & the column of the bags would always be B

 

[Fluff]

If you do not want to hard code the row, then there has to be some way of deciding which row it should be.
How are you going to do that?

 

[Akw47]

If you do not want to hard code the row, then there has to be some way of deciding which row it should be.
How are you going to do that?

Yea I thought it would be based on the scanned specific number row. E.G i scan 6954, it should find where the number is in the excel sheet. if it is at row 2, row 2 would be the row to highlight.

 

[Fluff]

But how are you finding the correct row?

 

[Akw47]

But how are you finding the correct row?

when the scanned specific number matches with the specific number saved in the excel file. (my thoughts)

Or would it be better if my active selection will change to the row where the number matches, so the correct row can be found. (after thinking abt your concerns)

 

[Fluff]

If you haven't figured out how to find the correct row, I suggest you do that before worrying about hard coding the row/cell.

 

[Akw47]

If you haven't figured out how to find the correct row, I suggest you do that before worrying about hard coding the row/cell.

Yep. I would figure it out first. Thanks for your help btw. You really helped to guide and show me which direction to look upon. Thanks thanks

 

[Fluff]

You're welcome & thanks for the feedback.