Looping file range

anichols

Board Regular
Joined
Mar 11, 2021
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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:
VBA 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).
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,245
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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

Board Regular
Joined
Mar 11, 2021
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,245
Office Version
  1. 365
Platform
  1. Windows
Easily fixed by wrapping in an IF ...

VBA Code:
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

Board Regular
Joined
Mar 11, 2021
Messages
67
Office Version
  1. 365
Platform
  1. Windows
Easily fixed by wrapping in an IF ...

VBA Code:
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:
VBA Code:
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 :)
 

Forum statistics

Threads
1,141,044
Messages
5,703,919
Members
421,321
Latest member
blusky4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top