VBA Loop Across Multiple Worksheets

Question

I realize there are 7 questions related to this same topic, but I can't figure this out for the life of me (stupidity, I guess lol). I am trying to run a VBA macro that utilizes to worksheets. I need the macro to do the following:

Go through the "Category" column in sheet "Database"
Save those 'String' values
Run values from "Category" column against "Retail Trade" column in sheet "Industry"
If there is a match between the two string type values, paste the value "Retail Trade" into column J of sheet "Database"

This is what I have so far:

Sub IndustryMatch()

   Dim ws As Worksheet
   Dim sh1 As Worksheet
   Dim sh3 As Worksheet
   Dim Wbk As Workbook

   Set Wbk = ActiveWorkbook
   Set sh1 = Wbk.Sheets("Database")
   Set sh3 = Wbk.Sheets("Industry")

   sh1.Activate

   Dim row As Integer
   Dim col As Integer
   'Dim currentK As String

   Dim numRowsK As String


   numRowsK = Wbk.Worksheets("Database").Range("K2",                                                                           

    Range("K2").End(xlDown)).Rows.Count

For a = 1 To numRowsK + 1 'start at 1 because it is the first row. This will loop through Column K

    'currentK = Wbk.Sheets("Database").Cells(k, "Category").Value 'Save the current value in column K

    sh3.Activate
    numRowsG = Wbk.Worksheets("Industry").Range("G2", Range("G2").End(xlDown)).Rows.Count

    For c = 1 To numRowsG + 1 'start at 1 because it is the first row. This will loop through Column G

        If (Wbk.Sheets("Industry").Cells(g, "G").Value = Wbk.Sheets("Database").Cells(k, "K").Value) Then 'Check if the col G value is equal to the current col K value.
            Wbk.Sheets("Database").Cells(j, "J").Value = Wbk.Sheets("Database").Cells(g, "G").Value  'If so copy to column J

        End If

    Next c

Next a


End Sub

Any help on how to achieve this correctly would be REALLY appreciated. Thank you so much.

EDIT: This is correct code now, but needing to add one more 'For Loop' to go through another column. I get no errors, but when I step into it, the code skips the 'For Loop' I added:

Sub IndustryMatch()

Dim wBk As Workbook

    Set wBk = ActiveWorkbook

    Dim dbSht As Worksheet
    Set dbSht = wBk.Sheets("Database")

    Dim indSht As Worksheet
    Set indSht = wBk.Sheets("Industry")

    Dim numRowsK As String

    numRowsK = dbSht.Range("K2", dbSht.Range("K2").End(xlDown)).Rows.Count

    Dim g As Integer
    Dim k As Integer
    Dim i As Integer

    For k = 1 To numRowsK + 1                                                ' start at first row. loop Column K

        numRowsG = indSht.Range("G2", Range("G2").End(xlDown)).Rows.Count

        For g = 1 To numRowsG + 1                                            ' start at first row. loop Column G

            If (indSht.Cells(g, "G").Value = dbSht.Cells(k, "K").Value) Then ' Check if the col G value is equal to the current col K value.
                dbSht.Cells(k, "J").Value = "Retail Trade"

        numRowsI = indSht.Range("I2", Range("I2").End(xlDown)).Rows.Count

          **For i = 1 To numRowsI + 1                                            ' start at first row. loop Column I

            If (indSht.Cells(i, "I").Value = dbSht.Cells(k, "K").Value) Then ' Check if the col I value is equal to the current col K value.
                dbSht.Cells(k, "J").Value = "Services"**

            End If

            Next i

            End If

        Next g

    Next k


End Sub

jsotola jsotola · Accepted Answer · 2017-07-21T01:47:28

i rewrote your code

you declared some sheet objects, but you did not make use of them

the pointers to the columns had messed up names

the _numRows = _ line was split into two

i have not tested your code, but it appears to be ok otherwise

one thing ... the code may take a long time to run if you a lot of data in both of the columns

i changed the code to copy to same row in column J as in column K

Sub IndustryMatch()

    Dim wBk As Workbook
    Set wBk = ActiveWorkbook

    Dim dbSht As Worksheet
    Set dbSht = wBk.Sheets("Database")

    Dim indSht As Worksheet
    Set indSht = wBk.Sheets("Industry")

'   dbSht.Activate                                                           ' no need for this

'   Dim currentK As String
    Dim numRowsK As String

    numRowsK = dbSht.Range("K2", dbSht.Range("K2").End(xlDown)).Rows.Count

    Dim g As Integer
    Dim k As Integer

    For k = 1 To numRowsK + 1                                                ' start at first row. loop Column K
'       currentK = dbSht.Cells(k, "Category").Value                          ' Save the current value in column K
'       indSht.Activate                                                      ' no need for this

        numRowsG = indSht.Range("G2", Range("G2").End(xlDown)).Rows.Count

        For g = 1 To numRowsG + 1                                            ' start at first row. loop Column G

            If (indSht.Cells(g, "G").Value = dbSht.Cells(k, "K").Value) Then ' Check if the col G value is equal to the current col K value.
                dbSht.Cells(k, "J").Value = dbSht.Cells(g, "G").Value        ' If so copy to column J
            End If
        Next g
    Next k
End Sub

VBA Loop Across Multiple Worksheets

1 Answers