Loops and Nested Loops

[Shah]

Dear All,

I have written a macro and having hard time running nested Loops properly. Please see the Code below:
The aim is to copy data from a Data worksheet based on filters (one filter value is taken from Template and column number corresponds to this) and paste value to Template in the correct column and row. Row values are different i.e. not a simple count, need (9, 10, 14, 15, 85, 86 etc).
Because my nested loop does not work I have given x (need 9, 10, 14, 15, 85, 86 etc) and z (need 12, 13, 14, 15) numbers. My other code runs perfectly and copies data looping through columns etc. Please let me know what changes I could make to run nested loops rather than copy and paste the same code several times.

Option Explicit
Sub LoopOne()
    Dim sh As Worksheet 'Macro worksheet
    Dim wb As Workbook 'Template
    Dim ToSht As Worksheet 'Current Month Data sheet on Template
    Dim var As Variant 'Sheet Name
    Dim FrmSht As Variant
    Dim j As Integer
    Dim x As Integer
    Dim y As Integer
    Dim z As Integer
    Dim lw As Long
    Dim Num As Integer
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
   
    'Open Template and data files
    Set sh = Sheet2 ' Macro worksheet
    Set wb = Workbooks.Open(sh.[E2]) ' Open Template workbook
    Set ToSht = Worksheets(sh.[C2].Value) ' Current month data sheet on Template
   
    j = 3 'row number on macro sheet to pick up the file path
    Set wb = Workbooks.Open(sh.Range("E" & j)) 'File Path & Name for data file
    var = sh.Range("C2", sh.Range("C" & Rows.Count).End(xlUp)) ' Select sheet name from Macro Worksheet
    Set FrmSht = wb.Sheets(var(j - 1, 1))
    Num = 45
    For y = 10 To Num 'Column numbers to loop through from Template
        lw = FrmSht.Range("A" & Rows.Count).End(xlUp).Row
        FrmSht.Range("A6:R" & lw).AutoFilter Field:=1, Criteria1:=ToSht.Cells(2, y).Value 'Filter on datasheet
        FrmSht.Range("A6:R" & lw).AutoFilter Field:=3, Criteria1:="TOTAL INVESTMENTS" 'Filter on datasheet
        x = 9 'row number on the Template
        z = 12 'column number on the datasheet
        ToSht.Cells(x, y) = Application.WorksheetFunction.Sum(FrmSht.Range(Cells(5, z), Cells(500, z)).SpecialCells(xlCellTypeVisible)) 'sum column after filter is applied
        ToSht.Cells(x, y) = ToSht.Cells(x, y).Value - ToSht.Cells(12, y).Value
        FrmSht.AutoFilterMode = False
    Next
    wb.Close 'Close the workbook
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Thanks,
Shah.

 

[David Evans]

If I'm understanding your issue correctly ...

You can build a table which holds your variable values x and z, and step through the table. I've done this in the past with a more complex collection of variables and it worked well. You can derive the variables by formula, if you want the process to be more flexible.

I'll see if i can locate some of the code - I may not have it here though ....

 

[p45cal]

Not quite sure what you want so I'll give a general answer. Try:

Sub blah()
TemplateRows = Array(9, 10, 14, 15, 85, 86)
DataShtColumns = Array(12, 13, 14, 15, 19, 23)
i = LBound(DataShtColumns)
For Each DestnRow In TemplateRows
  SourceColumn = DataShtColumns(i)
  MsgBox "Source Column is " & SourceColumn & vbLf & "Destination Row is " & DestnRow
  i = i + 1
Next DestnRow
End Sub

or:

Sub blah2()
TemplateRows = Array(9, 10, 14, 31, 85, 86)
DataShtColumns = Array(12, 13, 14, 15, 19, 23)
For i = LBound(TemplateRows) To UBound(TemplateRows)
  SourceColumn = DataShtColumns(i)
  DestnRow = TemplateRows(i)
  MsgBox "Source Column is " & SourceColumn & vbLf & "Destination Row is " & DestnRow
Next i
End Sub

then substitute SourceColumn for z and DestnRow for x in your lines of code and remove the msgBox line (and obviously adjust the values in each array?
Or use the values directly using DataShtColumns(i) and TemplateRows(i):

Sub blah3()
TemplateRows = Array(9, 10, 14, 31, 85, 86)
DataShtColumns = Array(12, 13, 14, 15, 19, 23)
For i = LBound(TemplateRows) To UBound(TemplateRows)
  MsgBox "Source Column is " & DataShtColumns(i) & vbLf & "Destination Row is " & TemplateRows(i)
Next i
End Sub

 

[David Evans]

Not quite sure what you want so I'll give a general answer. Try:

Sub blah()
TemplateRows = Array(9, 10, 14, 15, 85, 86)
DataShtColumns = Array(12, 13, 14, 15, 19, 23)
i = LBound(DataShtColumns)
For Each DestnRow In TemplateRows
  SourceColumn = DataShtColumns(i)
  MsgBox "Source Column is " & SourceColumn & vbLf & "Destination Row is " & DestnRow
  i = i + 1
Next DestnRow
End Sub

or:

Sub blah2()
TemplateRows = Array(9, 10, 14, 31, 85, 86)
DataShtColumns = Array(12, 13, 14, 15, 19, 23)
For i = LBound(TemplateRows) To UBound(TemplateRows)
  SourceColumn = DataShtColumns(i)
  DestnRow = TemplateRows(i)
  MsgBox "Source Column is " & SourceColumn & vbLf & "Destination Row is " & DestnRow
Next i
End Sub

then substitute SourceColumn for z and DestnRow for x in your lines of code and remove the msgBox line (and obviously adjust the values in each array?
Or use the values directly using DataShtColumns(i) and TemplateRows(i):

Sub blah3()
TemplateRows = Array(9, 10, 14, 31, 85, 86)
DataShtColumns = Array(12, 13, 14, 15, 19, 23)
For i = LBound(TemplateRows) To UBound(TemplateRows)
  MsgBox "Source Column is " & DataShtColumns(i) & vbLf & "Destination Row is " & TemplateRows(i)
Next i
End Sub

Great solution - much simpler than my approach i was going to dig up ...

 

[Shah]

p45cal and David thank you for this. I will give this a try. To explain a bit further:
I have a Template which has about 45 columns with different headings (numerical). Each column has 120 data rows.
There are several Data files. The macro will copy data and paste into Template from these files.
The Heading on Template is one of the filters for Data file (the other filters are hard coded).
Macro should go through each column heading in Template and get the data from Data file corresponding the heading. This part of my macro works fine (loops fine). The next part is where Macro needs to copy data from a different column in Data file and paste it to the same column in Template but different row.
When I use your code and incorporate into mine it gives Expected Array error (not sure why).

Option Explicit
Sub LoopOne()
Dim sh As Worksheet 'Macro worksheet
Dim wb As Workbook 'Template
Dim ToSht As Worksheet 'Current Month Data sheet on Template
Dim var As Variant 'Sheet Name
Dim FrmSht As Variant
Dim j As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim lw As Long
Dim Num As Integer
Dim TemplateRows As Integer
Dim DataShtColumns As Integer
Dim i As Integer
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    'Open Template and data files
 Set sh = Sheet2 ' Macro worksheet
 Set wb = Workbooks.Open(sh.[E2]) ' Open Template workbook
 Set ToSht = Worksheets(sh.[C2].Value) ' Current month data sheet on Template

    j = 3 'row number on macro sheet to pick up the file path
    Set wb = Workbooks.Open(sh.Range("E" & j)) 'File Path & Name for data file
    var = sh.Range("C2", sh.Range("C" & Rows.Count).End(xlUp)) ' Select sheet name from Macro Worksheet
    Set FrmSht = wb.Sheets(var(j - 1, 1))
    Num = 45
    For y = 10 To Num 'Column numbers to loop through from Template
        lw = FrmSht.Range("A" & Rows.Count).End(xlUp).Row
        TempateRows = Array(9, 10, 14, 15)
        DataShtColumns = Array(12, 13, 14, 15)
        For i = LBound(TemplateRows) To UBound(TemplateRows)
            x = TemplateRows(i)  'row number on the Template
            z = DataShtColumns(i) 'column number on the datasheet
            FrmSht.Range("A6:R" & lw).AutoFilter Field:=1, Criteria1:=ToSht.Cells(2, y).Value 'Filter on datasheet
            FrmSht.Range("A6:R" & lw).AutoFilter Field:=3, Criteria1:="TOTAL INVESTMENTS" 'Filter on datasheet
            ToSht.Cells(x, y) = Application.WorksheetFunction.Sum(FrmSht.Range(Cells(5, z), Cells(500, z)).SpecialCells(xlCellTypeVisible)) 'sum column after filter is applied
            ToSht.Cells(x, y) = ToSht.Cells(x, y).Value - ToSht.Cells(12, y).Value
            FrmSht.AutoFilterMode = False
        Next i
    Next
    wb.Close 'Close the workbook

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[p45cal]

change to:
Dim TemplateRows() As Integer
Dim DataShtColumns() As Integer
(btw As Long might be better)
or:
Dim TemplateRows
Dim DataShtColumns

There's a typo:
TempateRows = Array(9, 10, 14, 15)
should be:
TemplateRows = Array(9, 10, 14, 15)

The two lines:
TemplateRows = Array(9, 10, 14, 15)
DataShtColumns = Array(12, 13, 14, 15)
can be before and outside the For y loop (they don't need to be re-assigned 35 times).

 

[Shah]

This is great thank you p45cal. The typo was not there (As I typed in the Code) but I have changed the Dim as you suggested and taken the two lines outside the For y loop. Works well now.
Would like to add couple of other things to the code:
If I need to change the filter as well on similar concept. How do I use the array? Also I will need to apply the code to a few Data files to copy data with slightly different filters and different columns to copy data from. Would it be possible to incorporate those into the code or just repeat this code with slight variation?
Thanks,
Shah.

 

[p45cal]

If I need to change the filter as well on similar concept. How do I use the array?

If you're talking about Field:=3 filter then you can have a text array in the same way:
Field3Filters=Array("Total Investments","Something Else","Costs")
then use the likes of:
FrmSht.Range("A6:R" & lw).AutoFilter Field:=3, Criteria1:=Field3Filters(j)
where j is a value you control somehow.

Also I will need to apply the code to a few Data files to copy data with slightly different filters and different columns to copy data from. Would it be possible to incorporate those into the code or just repeat this code with slight variation?

In the same vein, depending on how you tell the datafiles apart you can do something along the lines of:

If myDataFilename = "hello.xlsx" Then
  Field3Filters = Array("Total Investments", "Something Else", "Costs")
Else
  Field3Filters = Array("Total Investments", "Weird stuff", "Costs2")
End If

If there are more variations then:

Select Case myDataFilename
  Case "hello.xlsx"
    Field3Filters = Array("Total Investments", "Something Else", "Costs")
  Case "there.xlsx"
    Field3Filters = Array("Total Investments", "Weird stuff", "Costs2")
  Case "anothername.xlsx"
    Field3Filters = Array("Investments", "Normal stuff", "Costs3")
End Select

or if there are only minor differences:

Field3Filters = Array("Total Investments", "Something Else", "Costs")
Select Case myDataFilename
  Case "there.xlsx"
    Field3Filters(2) = "Weird stuff"
  Case "anothername.xlsx"
    Field3Filters(1) = "Normal stuff"
    Field3Filters(2) = "Costs3"
End Select

Also, with arrays of this sort and their indices, make sure you know whether they start at 0 or 1 (zero-based or 1-based arrays). You will see that in my first codes in my first message in this thread it didn't matter because I used LBound and UBound.

 

[Shah]

Thanks a lot p45cal. Great help. I have managed to write the following code. I am not sure how elegant this is but would appreciate your feedback. I have introduced Fiel3Filters and put them in an array and used i to control it as well which is fine for this particular case (i couldn't make the if statement to work properly).

 Option Explicit
Sub Test()
Dim sh As Worksheet        'Macro worksheet
Dim wb As Workbook          'Template
Dim ToSht As Worksheet      'Current Month Data sheet on Template
Dim var As Variant          'Sheet Name
Dim FrmSht As Variant
Dim j As Integer
Dim y As Long
Dim lw As Long
Dim Num As Integer
Dim TempRows
Dim DataCol
Dim Field2Filters
Dim Field3Filters
Dim i As Long

Application.DisplayAlerts = False
Application.ScreenUpdating = False

'Open Template and data files

    Set sh = Sheet2 ' Macro worksheet
    Set wb = Workbooks.Open(sh.[E2])            ' Open Template workbook
    Set ToSht = Worksheets(sh.[C2].Value)  ' Current month data sheet on Template
    
j = 4
    Set wb = Workbooks.Open(sh.Range("E" & j)) 'File Path & Name for data file
    var = sh.Range("C2", sh.Range("C" & Rows.Count).End(xlUp)) ' Select sheet name from Macro Worksheet
    Set FrmSht = wb.Sheets(var(j - 1, 1))
    TempRows = Array(9, 10, 14, 15, 85, 86)
    DataCol = Array(12, 13, 14, 15, 14, 15)
    Field3Filters = Array("TOTAL INVESTMENTS", "TOTAL INVESTMENTS", "TOTAL INVESTMENTS", "TOTAL INVESTMENTS", "TOTAL CASH", "TOTAL CASH")
        For y = 10 To Num
            For i = LBound(TempRows) To UBound(TempRows)
                lw = FrmSht.Range("A" & Rows.Count).End(xlUp).Row
                FrmSht.Range("A6:R" & lw).AutoFilter Field:=1, Criteria1:=ToSht.Cells(2, y).Value
                FrmSht.Range("A6:R" & lw).AutoFilter Field:=3, Criteria1:=Field3Filters(i)
                ToSht.Cells(TempRows(i), y) = Application.WorksheetFunction.Sum(FrmSht.Range(Cells(5, DataCol(i)), Cells(500, DataCol(i))).SpecialCells(xlCellTypeVisible))
                FrmSht.AutoFilterMode = False
            Next i
            ToSht.Cells(9, y) = ToSht.Cells(9, y).Value - ToSht.Cells(12, y).Value
        Next
        wb.Close 'Close the workbook
    
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

I have another datasheet which needs a lot more filters with arrays...I have also come across that arrays are unable to perform if wildcards are introduced. I have one such need where for example I will need Field3Filters=Array("Total Investments","Something Else","Costs", "*wildcard*", "*wildcard*") with two wildcards in the array all applied to the same field at the same time. Not sure if there is another way to achieve this?

 

[p45cal]

Record a macro of you manually applying the filter with the 2 wildcards and getting the result you want then post the code snippet here.

 

[Shah]

I have the following: Not recorded but written by me and working fine: (although these are only 2 of 6 criteria needed, other ones are not wildcards).

AutoFilter Field:=4, Criteria1:="=*BANK*", Operator:=xlOr, Criteria2:="=*MISC*"

Thanks,
Shah.

 

[p45cal]

try like:

Field3Filters=Array("Total Investments","Something Else","Costs", "=*BANK*", "=*BANK*")

 

[Shah]

It doesn't like this. The filter either gives result for normal filters or wildcards. Not both. Don't know if there is another way to do this? Thanks

 

[p45cal]

Attach a workbook (with only dummy data in if necessary) which demonstrates this not working

 

[Shah]

Please find attached a test file with code plus some of dummy data. Thanks

 

[p45cal]

tweaking your code:

Sub LoopOne()
Dim lw As Long
Z = Array("hello", Array("=*BANK*", "=*MISC*", "GBL00001K"), "there")
lw = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Range("A1:D" & lw).AutoFilter Field:=3, Criteria1:=Z(1), Operator:=xlFilterValues
End Sub

 

[Shah]

Thank you p45cal.
This seems to be not working for me. I have now tweaked the template and copy and paste data into two different rows (wildcard data and other data).
I have question on Arrays:

TempRows = Array(75, 76, 104, 105)
    DataCol = Array(6, 6, 10, 10)
    Field4Filters = Array(Array("=*BANK*", "=*MISC*"), Array("GBL00001K", "AB001", "CHGBP1", "MAGBP1"))

Is it possible to have different size arrays and controlled by the same controller (i.e. in my code it is control by i). The Arrays are getting quite long...
Thanks

 

[p45cal]

Using your array above, this worked:

Sub LoopOne()
Dim lw As Long
Z = Array(Array("=*BANK*", "=*MISC*"), Array("GBL00001K", "AB001", "CHGBP1", "MAGBP1"))
For i = 0 To 1
  lw = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
  ActiveSheet.Range("A1:D" & lw).AutoFilter Field:=3, Criteria1:=Z(i), Operator:=xlFilterValues
Next i
End Sub

What you can't do is have more then 2 wildcard strings in a single filter column/field, wherever you may want to put them, or more than 2 strings if one of them is a wildcard string.
You can, however have as many literal strings as you wish, the non-existant ones just get ignored.
re:"different size arrays and controlled by the same controller". Yes.

BTW, Field4Filters, you're not then trying to put these in Field:=3?
You can of course, just making sure this isn't a simple error.

If you can create the filter you want manually, we'll be able to code it.
If you want to have more than 2 wildcard strings, we can code round it by creating a non-wildcard list from your data before applying the filter.

So what's your wish?

 

[Shah]

Thank you p45cal. I came across that by reading a few threads that I can't have two wildcards plus strings etc. Wildcards are there because otherwise it will be very difficult to code all the different categories so I decided to use two rows in the output i.e. use one filter for wildcards to get the data in Template row 1 and second filter for other strings to return data in Template row 2. This is working fine so I am happy with it.

The only problem now is that the code is very long! I can post the code if you want to have a look. I would like to reduce it a bit.

 

[p45cal]

so I decided to use two rows in the output i.e. use one filter for wildcards to get the data in Template row 1 and second filter for other strings to return data in Template row 2. This is working fine so I am happy with it.

It's difficult to get a mental picture of your output but my initial thoughts are that 2 rows in the output is messy and a little code-ingenuity can usually put things right. I can see that you won't want to put your workbook in the public domain but if you're happy for just me to see it, then ask me for a private email via a 'Conversation' here (I've started one up with you just now because I haven't used it before).

The only problem now is that the code is very long! I can post the code if you want to have a look. I would like to reduce it a bit.

Sure, if you don't do the above then post it here.

 

[Shah]

Posting data on forum is not ideal as it is a bit too sensitive. I will try to explain what I am trying to do:
There are four Data files and one Template. Data files have various data in different columns which needs copying to Template in different rows as well as columns depending on column header. I have attached a dummy file with the full macro. This is working fine and picking all the data necessary. As you may see some of the code is repeated as well as some arrays can be done better. Would be grateful to have your input. Thanks.

 

[p45cal]

Posting data on forum is not ideal as it is a bit too sensitive.

This is why I said:

I can see that you won't want to put your workbook in the public domain but if you're happy for just me to see it, then ask me for a private email via a 'Conversation' here (I've started one up with you just now because I haven't used it before).

Working blind and unable to test.
So the attached has a macro called g. It also has one called GetData1. That name because I noticed that each file (each value of J) undergoes almost the same process in the 1st section of each file's processing. It won't work first time I'm certain but it will give you an idea. You may even be able to incorporate some of the other file processing into another similar macro (GetData2?) or maybe even GetData1 still.
The advantage if you go down this route is that when you discover an error, or make a change, you don't have to go trawling through multiple bits of code to find everywhere to make the change; you only have to change the code in one place.

You'll also see I deleted some repeating lines where values aren't going to change, and rearranged some filter-adding lines so they were executed as few times as necessary - there's no need to keep resetting a filter to the same value inside the deepest nested loop.

 

[Shah]

This is great p45cal. Much appreciate the help. This has taught me many things. I have amended the macro and applied your GetData1/2 idea. The macro runs fine (had to make a few changes but tested the entire script and works fine).
I have question regarding GetData1. Is it possible to apply the same Conditional statement to the Filters as well so that I can have only one GetData macro with multiple filter fields i.e. Apply Field1, Field2, Field3 or just only Field1 or Field2 etc?

 

[p45cal]

Of course it is; it's down to your ingenuity - but be aware that it may not be worth doing - if you code in too many ifs and buts it may get too complicated to understand/edit/maintain the code later.

 

[Shah]

I have redone the code and GetData macro code is below: (hopefully not too many if statements). This has cut down the code quite a bit.

Sub GetData(FrmSht, ToSht, TempRows, DataCol, FieldFilters1, FieldNo1, AddFilter1, FieldFilters2, FieldNo2, AddFilter2, rngStr, lcol, Add12To9, Add12To77)
Dim lRow As Long
Dim y As Long
Dim i As Long

lRow = FrmSht.Range("A" & Rows.Count).End(xlUp).Row  ' Find the last row on DataSheet
For y = 6 To lcol  ' Column F to as long as there is data
  FrmSht.Range(rngStr & lRow).AutoFilter Field:=1, Criteria1:=ToSht.Cells(2, y).Value  ' Autofilter on Column A
    For i = LBound(TempRows) To UBound(TempRows)  ' Loop on row numbers in array to return data
      If AddFilter1 Then FrmSht.Range(rngStr & lRow).AutoFilter Field:=FieldNo1, Criteria1:=FieldFilters1(i), Operator:=xlFilterValues  ' Autofilter on a Column based on FieldFilters1
      If AddFilter2 Then FrmSht.Range(rngStr & lRow).AutoFilter Field:=FieldNo2, Criteria1:=FieldFilters2(i), Operator:=xlFilterValues ' Autofilter on a Column based on FieldFilters2
      ToSht.Cells(TempRows(i), y) = WorksheetFunction.Sum(FrmSht.Range(FrmSht.Cells(5, DataCol(i)), FrmSht.Cells(lRow, DataCol(i))).SpecialCells(xlCellTypeVisible))  ' Sum filtered Column and paste value in Template
    Next i
  If Add12To9 Then ToSht.Cells(9, y).Value = ToSht.Cells(9, y).Value - ToSht.Cells(12, y).Value  'Subtract Value in Cell 12 from Cell 9
  If Add12To77 Then ToSht.Cells(77, y).Value = ToSht.Cells(77, y).Value - ToSht.Cells(12, y).Value  'Subtract Value in Cell 12 from Cell 77
Next
FrmSht.ShowAllData  'clears filters
End Sub

The next step is as you have suggested already to reduce the first part of the macro with J repetition. For now I have 4 such repetitions which may increase in future. How do you suggest I go about doing that? Thanks