Need VBA code with "building" as string for "=len(a1)-len(substitute(a1,",",""))+1"

[Kichan]

Hi
I have a formula =len(a1)-len(substitute(a1,",",""))+1 please give a vba code for this formula with "building" as string

New Microsoft Office Excel Worksheet (4).xlsx
	A	B
1	134-1,134-5,136-8	3
2	134-1,134-5	2
3	134-1	1
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

Thank you

 

[Peter_SSs]

please give a vba code for this formula with "building" as string

I'm not exactly sure what you meant by that, but if you are just trying to replicate the formula results using vba, then would this be any use?

Sub Count_Items()
  Dim a As Variant
  Dim i As Long
  
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = UBound(Split(a(i, 1), ",")) + 1
    Next i
    .Offset(, 1).Value = a
  End With
End Sub

 

[Kichan]

I'm not exactly sure what you meant by that, but if you are just trying to replicate the formula results using vba, then would this be any use?

Sub Count_Items()
  Dim a As Variant
  Dim i As Long
 
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = UBound(Split(a(i, 1), ",")) + 1
    Next i
    .Offset(, 1).Value = a
  End With
End Sub

sorry brother .. ok now i will tell you what was my real requirment was

I want to get total number of items before comma "," in a single cell . that is in cell A1 I wrote134-1,134-5,136-8 in this case the items before "," is "3" so the total number of items in that cell is "3" .. like all other cells items before comma is counted

one more thing can you make this code ("building" as string) or ("kich" as string) with out declaring (Dim) items .. so i can use it in any cell as =kich(A1)
thanks

 

[Peter_SSs]

so i can use it in any cell as =kich(A1)

How would that be an advantage over using the standard worksheet formula from post #1 since worksheet functions are usually more efficient than a user-defined vba function equivalent?

 

[Phuoc]

Try this:

Public Function kich(ByVal s As String) As Long
If Len(s) > 0 Then kich = UBound(Split(s, ",")) + 1
End Function

 

[Peter_SSs]

How would that be an advantage over using the standard worksheet formula from post #1 since worksheet functions are usually more efficient than a user-defined vba function equivalent?

Post #5 provides a good example of my point. Tested on the same range of data several times, the udf took almost 10 times as long to calculate my test range than the post #1 formula.
Whilst the difference may not be a problem unless the ranges are very large, I just don't see any advantage of using such a udf over the worksheet formula.

 

[Kichan]

Post #5 provides a good example of my point. Tested on the same range of data several times, the udf took almost 10 times as long to calculate my test range than the post #1 formula.
Whilst the difference may not be a problem unless the ranges are very large, I just don't see any advantage of using such a udf over the worksheet formula.

Thank you bro for your reply ... I have started a VBA macros online course , and just a beginner ... From this platform getting lot of solutions from different angles on the same question .. This is really helping me to improve ..

Thank you .. have a good day brother