Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Loop through data and build an array

  1. #11
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,211
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Loop through data and build an array

    Not overly neat, but seems to do what you want.
    Code:
    Sub delta()
       Dim i As Long, j As Long
       Dim Ar1 As Areas, Ar2 As Areas
       Dim Ary As Variant, x As Variant
       
       Ary = Array("Work", 2, "Sick", 3, "Vacation", 4, "Meal Premium", 5)
       With Range("C:C")
          .Replace "Name", "=xxxName", xlWhole, , False, , False, False
          Set Ar1 = .SpecialCells(xlFormulas, xlErrors).Areas
          .Replace "=xxxName", "Name", xlWhole, , False, , False, False
       End With
       With Range("M:M")
          .Replace "Pay", "=xxxPay", xlPart, , False, , False, False
          Set Ar2 = .SpecialCells(xlFormulas, xlErrors).Areas
          .Replace "=xxx", "", xlPart, , False, , False, False
       End With
    
       With Sheets("Sheet2")
          For i = 1 To Ar1.Count
             .Cells(i + 1, 1) = Ar1(i).Offset(1)
             For j = 1 To Ar2(i).Offset(1, 2).CurrentRegion.Rows.Count
                x = Application.Match(Ar2(i).Offset(j, 2), Ary, 0)
                If Not IsError(x) Then
                   .Cells(i + 1, Ary(x)) = Ar2(i).Offset(j, 10).Value
                   If x = 1 Then .Cells(i + 1, 6) = Ar2(i).Offset(j, 12).Value
                Else
                   .Cells(i + 1, 6) = Ar2(i).Offset(1, 12).Value
                End If
             Next j
          Next i
       End With
    End Sub
    Last edited by Fluff; Jul 20th, 2019 at 10:16 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  2. #12
    Board Regular
    Join Date
    Feb 2002
    Posts
    241
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop through data and build an array

    Fluff,

    My apologies for such a late thank you. This worked great and was lightning fast. Can you explain how it works? I'm not quite sure how the top half and bottom half work together (obviously I'm not a programmer and envy those of you who are). I have many uses for this so to the degree I can understand the mechanics, I'll be able to modify it for future uses.

    P.S. Really like the rose. My grandmother had a rose garden and pictures of them bring back the fondest of memories. Chippenham, is that in England? If so hope you all are surviving your record heat. Where I'm at it's supposed to be 110 F tomorrow, but still 8 degrees shy of our record.

  3. #13
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,211
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Loop through data and build an array

    You're welcome & thanks for the feedback.

    I'll write up a brief description, when I get a moment.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •