CODEWITHSUNDEEP
excelvba
Paul Hofer
Paul Hofer

Reputation: 1

VBA looping a file for every item in an array

Excel VBA I will try my best to have this make sense.

I have set my array for 29 items (equipment ID T0001-T0028).

Essentially I have a report that has maintenance write-ups for these pieces of equipment. I am trying to create a loop that will go through the file and find each time the equid ID is listed and then i will use the left/right/mid functions to extract data from the file. However each equip ID will be listed multiple times so they way i am picturing this happening is for equip id T0001 the procedure will go through the entire file finding and extracting each time "T0001" is listed and then go to "T0002" and go through the entire file and so...

I know it will be a loop of some sort but I am so confused on whether to loop the file or loop the array. Can anyone help. 

Sub EquipArray()
Dim sampleArr() As Variant
Dim i As Integer
Dim rng As Range, cell As Range

i = 1
Set rng = Range("A2:A29")

sampleArr = rng


End Sub

Upvotes: 0

Views: 213

Answers (2)

Thiago
Thiago

Reputation: 91

If I understood it right, it will make no difference, since you'll have to loop it [number of files] * [number of items] (or [number of items] * [number of files], wich is the same). Couldn't you use a formula to classify each file beforehand, so you don't have to test it against each item?

Upvotes: 0

Doug Coats
Doug Coats

Reputation: 7107

this is a loop that finds duplicates. alter to your needs

Private Sub this()

Dim rng As Range
Dim rCell As Range
Dim this As String
Dim arr(9)

Set rng = ThisWorkbook.Sheets("Sheet1").Range("a1:a10")

For Each rCell In rng.Cells
    this = rCell.Value
    For x = LBound(arr, 1) To UBound(arr, 1)
        If this = arr(x) Then
            rCell.Interior.ColorIndex = 7
            Exit For
        ElseIf this <> arr(x) And arr(x) = vbNullString Then
            arr(x) = this
            Exit For
        End If
    Next x
Next rCell

End Sub

Upvotes: 1

Related Questions