How to loop through rows, save these as variables and use them as variables VBA

Question

I'm trying to store values in sheets as a variable, and then go on to reference a sheet using that variable as well as use it to filter by.

This will be looped through until the program reaches the first empty cell.

The relevant code I have so far is:

Sub Program()

Dim i As Integer
i = 2

Do Until IsEmpty(Cells(i, 1))
Debug.Print i
    Sheets("Button").Activate
        Dim First As String
        First = Cells(i, 1).Value
        Debug.Print First

        Dim Second As String
        Second = Cells(i, 2).Value
        Debug.Print Second 

    'Filters my Data sheet and copies the data

    Sheets("DATA").Activate
    Sheets("DATA").Range("A1").AutoFilter _
        Field:=2, _
        Criteria1:=First 'Filters for relevant organisation
    Sheets("DATA").Range("A1").AutoFilter _
        Field:=6, _
        Criteria1:="=" 'Filters for No Response

    Sheets("DATA").Range("A1:H6040").Copy

    'This should loop through for each separate group

    Sheets(CStr(Second)).Select
    Range("A1").Select
        ActiveSheet.Paste
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

i = i + 1
Loop

Worksheets("DATA").AutoFilterMode = False

End Sub

I have changed the program significantly trying to add notation such as 'CStr' as there was an error at this line: Sheets(CStr(Second)).Select when it used to say Sheets(Second)).Select

and the debug.print's to see if it is actually working but it isn't logging to the Immediate Window.

Additionally, when I actually run it, no error comes up but nothing seems to happen.

Not sure what else to add, or what else to try. Cheers!

score 0 · Accepted Answer · edited May 23 '17 at 11:45

As a first remark, using (at least the first) sheet activation within the loop seems unnecessary, because the start of the loop is what determines which sheet is being used to control the flow of the loop.

Furthermore, I would argue that it is better to remove the sheet activation altogether, re: the discussion about .Select (the cases aren't the same, but the solution discussed herein works better for both .Select and .Activate in almost all instances): How to avoid using Select in Excel VBA macros.

Let's also see if we can refer to the table in the "DATA" sheet in a more direct manner, as well as do some errorchecking.

My suggestion:

Sub Program()

Dim i As Integer
Dim First, Second As String
Dim secondWs As Worksheet
Dim dataTbl As ListObject

i = 2
Set dataTbl = Worksheets("DATA").Range("A1").ListObject.Name 
' The above can be done more elegantly if you supply the name of the table

Sheets("DATA").Activate

Do Until IsEmpty(Cells(i, 1))
Debug.Print i
        First = Sheets("Button").Cells(i, 1).Value
        Debug.Print First

        Second = Sheets("Button").Cells(i, 2).Value
        Debug.Print Second 

    'Filters my Data sheet and copies the data

    dataTbl.AutoFilter _
        Field:=2, _
        Criteria1:=First 'Filters for relevant organisation
    dataTbl.AutoFilter _
        Field:=6, _
        Criteria1:="=" 'Filters for No Response

    Sheets("DATA").Range("A1:H6040").Copy

    'This should loop through for each separate group

    On Error Resume Next
    Set secondWs = Worksheets(Second)
    On Error GoTo 0

    If Not secondWs Is Nothing Then
        secondWs.Range("A1").PasteSpecial Paste:=xlPasteValues
    Else
        Debug.Print "Sheet name SECOND was not found"
    End If

i = i + 1
Loop

Worksheets("DATA").AutoFilterMode = False

End Sub

If you get any errors, please state which line it appears on and what the error message actually is.

Ref: http://www.mrexcel.com/forum/excel-questions/3228-visual-basic-applications-check-if-worksheet-exists.html#post13739

Thanks @Vegard for your help I got an error of "Invalid Use of Property" on this line: dataTb1.AutoFilter _ As far as I know the table has no name. It is a dynamic list that increases in size over time (which is why I redo the AutoFilter stuff each time). — Ryan Ward, Feb 09 '16 at 11:18
It's `dataTbl`, not `dataTb1`. And it finds whatever table is present in `A1`. But are you trying to say that you aren't using tables? — Vegard, Feb 09 '16 at 11:26
I should also add that the debug.Print isn't showing anything at all still, (it works in other subs). Additionally, 'Sheets("DATA").Range("A1:H6040").Copy' - the number 6040 is simply used because it is much larger than I'll probably ever need. In that sense would you have a suggestion to use something which simply references the relevant table? Cheers again! — Ryan Ward, Feb 09 '16 at 11:45
`Set dataTbl = Worksheets("DATA").Range("A1").ListObject.Name` this line will bind the variable `dataTbl` to whatever table `A1` is a part of. Add this reference correctly (see my previous comment) and see if it works. If not, you need to describe your errors more clearly. — Vegard, Feb 09 '16 at 13:07
Hey I must have asked strangely but you helped in a big way. I simply set `dataTbl = Worksheets("DATA").Range("A1")`. This made the whole loop work as expected so thanks again and with a couple of other changes which I can happily do this program is exactly where i need it :) — Ryan Ward, Feb 10 '16 at 09:29
Yeah, you might have had to remove the `.Name`. But glad you figured it out! — Vegard, Feb 10 '16 at 09:30

How to loop through rows, save these as variables and use them as variables VBA

1 Answers1