Ryan Ward February 2016

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

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!

Answers


Vegard February 2016

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

Post Status

Asked in February 2016
Viewed 1,832 times
Voted 14
Answered 1 times

Search




Leave an answer