If it looks like

[username123]

HI!

I am looping through a row, storing data in an array, but I only want one format.

Is there a way to say

If it looks like XX:XX:XX then store (where X are numbers)

or

If it looks like XXXXXXX then store (so a 7-number string)

THANKS!

:doh:
:think:
: pray2:

[nst1107]

Yes, there is a way. If you post a working file it will be easier to tell you more about how to do it.

[username123]

What I mentioned above are the two cases I am concerned with. Basically I am using this code:


Function UniqueItems(ArrayIn, Optional Count As Variant) As Variant
' Accepts an array or range as input
' If Count = True or is missing, the function returns the number
' of unique elements
' If Count = False, the function returns a variant array of unique
' elements

Dim Unique() As Variant ' array that holds the unique items
Dim Element As Variant
Dim i As Integer
Dim FoundMatch As Boolean

' If 2nd argument is missing, assign default value
If IsMissing(Count) Then Count = True

' Counter for number of unique elements
NumUnique = 0

' Loop thru the input array
For Each Element In ArrayIn
FoundMatch = False

' Has item been added yet?
For i = 1 To NumUnique
If Element = Unique(i) Then
FoundMatch = True
GoTo AddItem '(Exit For-Next loop)
End If
Next i

AddItem:
' If not in list, add the item to unique list
If Not FoundMatch Then
NumUnique = NumUnique + 1
ReDim Preserve Unique(NumUnique)
Unique(NumUnique) = Element
End If

Next Element

' Assign a value to the function
If Count Then UniqueItems = NumUnique Else UniqueItems = Unique
End Function

Right now it returns the header of the column as well, and I don't want that.

I got this code from another post on this forum, so I don't understand the exact workings

[georgiboy]

This will loop through a range set in "MyRange" and provide the cell address of any cells that meet your conditions (assuming you want to find a time format).

Sub LoopThrough()
Dim MyRange As Range, rCell As Range

Set MyRange = Sheet1.Range("A1:A500")

For Each rCell In MyRange.Cells

If rCell.NumberFormat = "h:mm:ss" Then
MsgBox rCell.Address & " = " & "h:mm:ss"
End If

If IsNumeric(rCell.Value) Then
If Len(rCell.Value) = 7 Then
MsgBox rCell.Address & " = 7 numbers long"
End If
End If

Next rCell

End Sub

Hope this helps