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?please give a vba code for this formula with "building" as string
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 wasI'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?
VBA Code: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
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?so i can use it in any cell as =kich(A1)
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.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?
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 ..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.