VBA looping a file for every item in an array

Question

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

Doug Coats · Accepted Answer · 2017-04-14 18:45:12Z

1

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

answered Apr 14, 2017 at 18:45

Doug Coats

7,2179 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Hofer Over a year ago

Thank you I will try it now

Thiago · Accepted Answer · 2017-04-14 18:49:09Z

0

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?

answered Apr 14, 2017 at 18:49

Thiago

9111 bronze badges

1 Comment

Paul Hofer Over a year ago

I am not allowed to upload pictures (that may make things easier to explain) but it is one file that will list the equip ID's many times. I am trying to write a code to extra data from the report but basically i need the equip ID's as "keys", for lack of a better word. The code will find the id number and then extract data from that line and move on to the next line looking for the ID # again. At the end of the file i need it to start over with the next ID #.

Collectives™ on Stack Overflow

VBA looping a file for every item in an array

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related