L
leungkong
I use below code to store the unique record in a array - sUCells()
and count the number of element of the array - iUVals
Since I use this code more than one time in my template.
To easy reading and maintain.
Can I set a function to return two objtct "sUCells() & iUVals" ?
Any suggest? Thanks.
Dim myrange As Range
Dim cell As Range
Dim j As Integer
Dim iNumCells As Integer
Dim iUVals As Integer
Dim sUCells() As String
Dim lastrow As Integer
lastrow = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A2:A" & lastrow)
iNumCells = myrange.Count
ReDim sUCells(iNumCells) As String
iUVals = 0
For Each cell In myrange
If cell.Text > "" Then
For j = 1 To iUVals
If sUCells(j) = UCase(cell.Text) Then
Exit For
End If
Next j
If j > iUVals Then
iUVals = iUVals + 1
sUCells(iUVals) = UCase(cell.Text)
End If
End If
Next cell
Set myrange = Nothing
Set cell = Nothing
If iUVals = 0 Then Exit Sub
For i = 1 To iUVals
...
Next i
and count the number of element of the array - iUVals
Since I use this code more than one time in my template.
To easy reading and maintain.
Can I set a function to return two objtct "sUCells() & iUVals" ?
Any suggest? Thanks.
Dim myrange As Range
Dim cell As Range
Dim j As Integer
Dim iNumCells As Integer
Dim iUVals As Integer
Dim sUCells() As String
Dim lastrow As Integer
lastrow = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A2:A" & lastrow)
iNumCells = myrange.Count
ReDim sUCells(iNumCells) As String
iUVals = 0
For Each cell In myrange
If cell.Text > "" Then
For j = 1 To iUVals
If sUCells(j) = UCase(cell.Text) Then
Exit For
End If
Next j
If j > iUVals Then
iUVals = iUVals + 1
sUCells(iUVals) = UCase(cell.Text)
End If
End If
Next cell
Set myrange = Nothing
Set cell = Nothing
If iUVals = 0 Then Exit Sub
For i = 1 To iUVals
...
Next i