"For Each" with arrays

[laterdater]

Hey all,



I am trying to pull header info from a sheet and put it into a destination sheet. The info is an alphanumeric part number. I am trying to put all part numbers that end in 2 in one cell, and all other part numbers in another (there are only two possible part numbers on my source sheet.)

I am having issues with the "For Each" part of my VBA:

For x = 1 To wkshtcount

    Set sh = ThisWorkbook.Sheets(x + 1)
    Set shdest = ThisWorkbook.Sheets(sh.name & (x + 1))
    Set rn = sh.UsedRange
    Set Names = sh.Range("A2:A" & Range("A1").End(xlDown).Row)
        
            For Each name In Names
                If name Like "*2" Then
                            shdest.Range("D7") = name
                            Else: shdest.Range("D8") = name

                End If
            Next name
Next x

Even when my source sheet has two part numbers, it only copies over one (in the right slot, though, so I know the "Like" portion of my code is working.)

I think the issue might be that it is not looping through Each name in Names. What could be the reason for this?

[charlesdh]

Hi,

Can you attach the file so can see it? This may help.

[laterdater]

I am still confused as to why my For Each didn't work, but I solved this by getting rid of my "For Each" and replacing it with the following:

    Set rn = sh.UsedRange
    k = rn.Rows.Count + rn.Row - 1
    For y = 2 To k
                If sh.Range("A" & y) Like "*2" Then
                            shdest.Range("D7") = sh.Range("A" & y)
                            Else: shdest.Range("D8") = sh.Range("A" & y)

                End If
    Next y

[charlesdh]

Hi,

Here' a bit of code using "For Each".
I found this doing a search.
If this does not help can you proved a workbook so we can better help you.

Sub WorksheetLoop()

         Dim WS_Count As Integer
         Dim I As Integer

         ' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
         WS_Count = ActiveWorkbook.Worksheets.Count

         ' Begin the loop.
         For I = 1 To WS_Count

            ' Insert your code here.
            ' The following line shows how to reference a sheet within
            ' the loop by displaying the worksheet name in a dialog box.
            MsgBox ActiveWorkbook.Worksheets(I).Name

         Next I

      End Sub