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]

How about

If .Tag = SumNums(Cells(9, 6)) Then

 

[Akw47]

Yep that changes work, but how do I code it such that the cells it reads will be highlighted automatically

 

[Akw47]

Yep that changes work, but how do I code it such that the cells it reads will be highlighted automatically

Instead of specifiying the row currently

 

[Fluff]

What's wrong with doing it like you have?
At some point you need to tell it which cell/row to look at.

 

[Akw47]

Oh I apologise. Because my future plan was that when I scan a product code & it matches, it will highlight the entire row that match. So if I specify it currently with rows(9). I am scared to face problems later on. Hope you understand ?

 

[Fluff]

Hope you understand

Nope.
How will you determin which cell to look at for the SumNums function?

 

[Akw47]

oh actually, I plan to use the sumnums function to scan through a specific entire column, so that the excel will know how many time the scan should take place. Which cell to look at is based on the scanned product. Eg. the barcode scanned matches with the row 5 column 4 of the data, then that row should be highlighted after the scanned frequency matches with the sumnums of that row.

 

[Fluff]

I'm afraid you have now lost me completely.

 

[Akw47]

hahahaha, um let my try to explain myself.

Specific Number / Bags
9699 1 White 1 Red

From the second column, I want to use the sumnums function to determine the number of times the specific number have to be scanned. When, the specific number matches with scanned number, it will be considered as 1 time. Before the entire row gets highlighted the determined number of scanned time have to be accomplished. did i explain it better ahhahaa?