CODEWITHSUNDEEP
vbaexcel
Max Anderson
Max Anderson

Reputation: 33

VBA Looping Through Multiple Worksheets

I am working on building code which can loop through a column (B5:B) on multiple worksheets to find matching values. If the Value on one worksheet's column (B5:B) is equal to a worksheet name, then the worksheet name is placed on the adjacent column (C5:C) to where the value was found. I am not a programmer, but I've been learning VBA to make this happen. So far I have tried unsuccessfully to use the For Next Loop (starting with the 3rd sheet), the For Each ws in Thisworkbook.sheets method. But I don't seem to be able to make it work. I've searched all over the internet for something similar, but no dice. Any suggestions would be greatly appreciated. 

Sub MatchingPeople()
    Dim c As Variant
    Dim lastrow As Long
    Dim i As Variant
    Dim g As Long
    Dim w As Long

    i = Sheets("Anthony").Name
    g = Sheets("Anthony").Cells(Rows.Count, "C").End(xlUp).Row

    For w = 3 To Sheets.Count
        lastrow = Sheets(w).Cells(Rows.Count, 2).End(xlUp).Row
        Set NewRang = Sheets("Anthony").Cells(g + 1, 3)
        On Error Resume Next
        With Sheets(w).Range(Cells(5, 2), Cells(lasty, 2))
            Set c = .Find(i, LookIn:=xlValues)
            If Not c Is Nothing Then
                firstaddress = c.Address
                Do
                    NewRang.Value = Sheets(w).Name
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> firstaddress
            End If
        End With
    Next w
End Sub

Upvotes: 2

Views: 262

Answers (2)

Max Anderson
Max Anderson

Reputation: 33

Sub Bygone()

Dim x As Long

Dim y As Long

Dim z As Long

Dim w As Long

Dim a As Long

Dim b As Long

Dim c As Long

Dim m As Long

a = Sheets.Count

For m = 3 To a

   x = Sheets(m).Cells(3, 3).Value

       For b = 3 To a

            w = Sheets(b).Cells(Rows.Count, 1).End(xlUp).row

                For z = 5 To w

                    y = Sheets(b).Cells(z, 1)

                        Select Case x

                            Case y
                              c =Sheets(m).Cells(Rows.Count,3).End(xlUp).Offset(1, 0).row
                              Sheets(m).Cells(c, 3).Value = Sheets(b).Name
                         End Select
                Next z

      Next b
Next m

End Sub

Upvotes: 0

paul bica
paul bica

Reputation: 10715

Here are 2 versions, one using the Find method like in your code, the other using a For loop

Option Explicit

Public Sub MatchingPeopleFind()
    Dim i As Long, lrColB As Long
    Dim wsCount As Long, wsName As String
    Dim found As Variant, foundAdr As String

    wsCount = Worksheets.Count
    If wsCount >= 3 Then
        For i = 3 To wsCount
            With Worksheets(i)
                wsName = .Name
                lrColB = .Cells(.Rows.Count, 2).End(xlUp).Row
                With .Range(.Cells(5, 2), .Cells(lrColB, 2))
                    Set found = .Find(wsName, LookIn:=xlValues)
                    If Not found Is Nothing Then
                        foundAdr = found.Address
                        Do
                            found.Offset(0, 1).Value2 = wsName
                            Set found = .FindNext(found)
                        Loop While Not found Is Nothing And found.Address <> foundAdr
                    End If
                End With
            End With
        Next
    End If
End Sub

Public Sub MatchingPeopleForLoop()
    Dim wsCount As Long, wsName As String, i As Long, j As Long

    wsCount = Worksheets.Count
    If wsCount >= 3 Then
        For i = 3 To wsCount
            With Worksheets(i)
                wsName = .Name
                For j = 5 To .Cells(.Rows.Count, 2).End(xlUp).Row
                    If .Cells(j, 2).Value2 = wsName Then .Cells(j, 3).Value2 = wsName
                Next
            End With
        Next
    End If
End Sub

Upvotes: 1

Related Questions