Looping file range

[anichols]

Hello all,


I'm trying to figure out how to do this properly, but making functional loops seems to be my VBA Kryptonite. I have this code:

Private Sub CommandButton2_Click()
Sheets("Summary").Range("A1").Value = FEP.TextBox1.Value
AllBanks
End Sub

that takes the number entered into the user form and places it in "A1" then runs the remaining code.

What I am trying to figure out how to do is to look at the "FilePaths" sheet and take each number that appears in column b (range 2-1200) and enter it into "Summary" - "A1" then run the code, then take the next value and do the same thing (essentially running the code up to 1200 times if every row in column B had a value).

 

[StephenCrump]

Perhaps something like this.

I don't know if you need the code to apply the 1,200 max, or whether you're confident that will always be fewer than 1,200 numbers?

Private Sub CommandButton2_Click()
    
    Dim i As Long, MyMax As Long
    Dim MyNumbers As Variant
    
    MyMax = 1200
    With Worksheets("FilePaths")
        MyNumbers = .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row).Value
    End With
    
    With Worksheets("Summary")
        For i = 1 To Application.Min(MyMax, UBound(MyNumbers))
            .Range("A1").Value = MyNumbers(i, 1)
            Call AllBanks
        Next i
    End With
    
End Sub

 

[anichols]

Perhaps something like this.

I don't know if you need the code to apply the 1,200 max, or whether you're confident that will always be fewer than 1,200 numbers?

Private Sub CommandButton2_Click()
   
    Dim i As Long, MyMax As Long
    Dim MyNumbers As Variant
   
    MyMax = 1200
    With Worksheets("FilePaths")
        MyNumbers = .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row).Value
    End With
   
    With Worksheets("Summary")
        For i = 1 To Application.Min(MyMax, UBound(MyNumbers))
            .Range("A1").Value = MyNumbers(i, 1)
            Call AllBanks
        Next i
    End With
   
End Sub

That does loop through, however, it tries to run even when there are no values in a row in the b column. which then pops errors in the rest of the code. I just need it to skip any blanks

 

[StephenCrump]

Easily fixed by wrapping in an IF ...

If MyNumbers(i, 1) <> "" Then
    .Range("A1").Value = MyNumbers(i, 1)
    Call AllBanks
End If

It's still not clear whether you need a 1,200 maximum? And if so, whether this is the maximum number of rows you pick up from FilePaths!B:B, or the maximum number of non-blanks for which you populate Summary!A1?

 

[anichols]

Easily fixed by wrapping in an IF ...

If MyNumbers(i, 1) <> "" Then
    .Range("A1").Value = MyNumbers(i, 1)
    Call AllBanks
End If

It's still not clear whether you need a 1,200 maximum? And if so, whether this is the maximum number of rows you pick up from FilePaths!B:B, or the maximum number of non-blanks for which you populate Summary!A1?

Row 1200 is as far as the data goes, however there are numerous blanks within that range. so counting to 1200 (blanks included) is fine.

In the interest of sharing, I added a line of code to the Allbanks sub that solves my issue as well:

If Workbooks(1).Worksheets("Summary").Range("A1").Value = "" Then
    Exit Sub
End If

Essentially this will just skip if the loop provided a blank and continue running the loop. Although I don't see a time lag, this approach may not be as efficient if I had 10,000+rows to loop.

(Just a thought as I type this, I think I could put an if statement before I call all banks that checks for a blank as well and the just returns next i...)

I suppose there's multiple ways to make it work