For Next Loop using UsedRange

[sitewolf]

For lead = 1 To 20
    CurrentFile = ActiveCell
    Workbooks.Open Filename:=CurrentFile
    ActiveWindow.Caption = "Graboid"

I have a file I use to grab information off a website I subscribe to.
I get daily emails containing a list, then I use that list to extract additional information for each item in the list.
But I never know how many will be in the list. Now, when it's me running the macro, I just edit the numbers to suit me, but I'm looking to set this up so someone can run this w/o having to deal with editing VB.................which means I should also add in some log in script now that I think about it.

Anyway, this is just a snippet...

 

[sitewolf]

Right now I'm just looking for a way to change 'For lead = 1 to 20' to something that loops thru however many leads are in the list that day

 

[TKB]

Not knowing what the list your looking at looks like, could you use a:

Do Until ActiveCell.Select = ""

Have the macro select the first cell of the list, run the macro then have it do a ActiveCell.Offset(1,0).Select

Then loop it?

 

[Jonmo1]

Perhaps

For lead = 1 To ActiveSheet.UsedRange.Rows.Count

 

[sitewolf]

hmm...both seemingly would work looking at them....but both want to continue beyond the end of my list...

Dim CurrentFile
    Application.DisplayAlerts = False
    Range("A1").Select
    For lead = 1 To 20
    CurrentFile = ActiveCell
    Workbooks.Open Filename:=CurrentFile
    ActiveWindow.Caption = "Graboid"
    Cells.Select
    With Selection
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.Offset(0, 0).Range("A38:e150").Select
    Selection.Copy

Column A will have a list of links. This macro visits each link on the list, copies info in a set range, and pastes that into my spread...then goes to the next link

 

[Jonmo1]

Can you post the entire code...

Try

lr = Cells(Rows.Count, "A").End(xlup).Row
For lead = 1 to lr

 

[sitewolf]

Sweet! Thank you, that works...and here's the full macro

I never have figured out a way to replace the range I copy (A38:E150 in this case) to web tables. In this case I know it's always table 2, 3, and 4 on the page I want...but, using this range has been fairly stable as well thus far. It's one thing to build a spread for your own use, where you know little tweaks you may need to make in certain instances...and a spread you want to hand someone who is lucky to be able to actually follow instructions on what buttons to push.

 Dim CurrentFile
    Application.DisplayAlerts = False
    Range("A1").Select
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    For lead = 1 To LR
    CurrentFile = ActiveCell
    Workbooks.Open Filename:=CurrentFile
    ActiveWindow.Caption = "Graboid"
    Cells.Select
    With Selection
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.Offset(0, 0).Range("A38:e150").Select
    Selection.Copy
    Windows("BC Alert Compiler.xlsm").Activate
    Sheets("raw").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Rows("1:5").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Offset(1, 11).Range("A1").Select
    Sheets("links").Select
    Selection.Offset(1, 0).Select
    Windows("Graboid").Activate
    ActiveWindow.Close
    Next lead
End Sub

 

[TKB]

My "Do Until Loop" assumes that once you have gone through all of the links, the cell under the last link in the list is a blank cell.

If that is not the case then replace the "" with "text in cell"

 

[sitewolf]

My "Do Until Loop" assumes that once you have gone through all of the links, the cell under the last link in the list is a blank cell.

If that is not the case then replace the "" with "text in cell"

Right, I caught that...and the cell should be blank...perhaps, because sometimes the list is shorter today than yesterday, that cell isn't TRULY blank, I'm not sure (tho that area does go thru a clear contents deal prior)
No worries, tho, I already thought of another macro that might benefit from your method

I've got another macro where I'd like the loop to continue as long as a certain cell is 1...or < 3