S
Seeker
I need to count numerical & text from A2:A65536 in “sheet B†and would like
to have the counting result shows in “sheet A†cell A1. I found a VBA posted
on 12 Jan 2007 which may fit my needs. However I am illiteracy to VBA and
don’t know how to embed following VBA to my current one which was done by
macro recorder. I also need help in replacing information to fit the range
name & sheet name by using this VBA.
Thanks.
Function CountValues(TheRange As Range) As Integer
Dim SaveValues(99) As Variant
Dim Index As Integer
Dim MaxIndex As Integer
Dim FoundValue As Boolean
Index = 0
MaxIndex = 0
Dim cell As Range
For Each cell In TheRange
If IsNumeric(cell) Then
TheValue = Val(cell.Value)
If MaxIndex = 0 Then
SaveValues(MaxIndex) = TheValue
MaxIndex = MaxIndex + 1
Else
FoundValue = False
Index = 0
While Not FoundValue And Index <= MaxIndex
FoundValue = (SaveValues(Index) = TheValue)
If Not FoundValue Then Index = Index + 1
Wend
If Not FoundValue Then
SaveValues(MaxIndex) = TheValue
MaxIndex = MaxIndex + 1
End If
End If
End If
Next
CountValues = MaxIndex
End Function
to have the counting result shows in “sheet A†cell A1. I found a VBA posted
on 12 Jan 2007 which may fit my needs. However I am illiteracy to VBA and
don’t know how to embed following VBA to my current one which was done by
macro recorder. I also need help in replacing information to fit the range
name & sheet name by using this VBA.
Thanks.
Function CountValues(TheRange As Range) As Integer
Dim SaveValues(99) As Variant
Dim Index As Integer
Dim MaxIndex As Integer
Dim FoundValue As Boolean
Index = 0
MaxIndex = 0
Dim cell As Range
For Each cell In TheRange
If IsNumeric(cell) Then
TheValue = Val(cell.Value)
If MaxIndex = 0 Then
SaveValues(MaxIndex) = TheValue
MaxIndex = MaxIndex + 1
Else
FoundValue = False
Index = 0
While Not FoundValue And Index <= MaxIndex
FoundValue = (SaveValues(Index) = TheValue)
If Not FoundValue Then Index = Index + 1
Wend
If Not FoundValue Then
SaveValues(MaxIndex) = TheValue
MaxIndex = MaxIndex + 1
End If
End If
End If
Next
CountValues = MaxIndex
End Function