Problem with Loop

[olympiac]

The folowing code is part of a code that opens the file listed in cell A1 to run a Macro to apply appropriate format and once completed the value in cell A1 is deleted in order to run the macro in the new file listed in Cell A1 that was before in Cell A2.
Why doesn't the code below do the loop?

Do
Range("A1").Select
If IsEmpty(ActiveCell) Then

MsgBox ("completed")
Exit Sub
Else
Workbooks.Open FileName:=(Range("E3") & Range("F3") & Range("A1"))

Call macro_name

Range("A1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End If
MsgBox ("completed")
Range("A1").Select
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))

 

[olympiac]

SOLVED:

Loop Until IsEmpty(ActiveCell) = True

 

[Peter_SSs]

SOLVED, but here's a few things that might help for the future. (I had typed most of this before seeing your 'solved' note)

When posting code, please use Code Tags to show indented code. Not doing so makes your code much harder to follow and debug. My signature block shows how to do this.

Your code is looping until cell B2 is empty, I suspect that is not what you meant. I assume that you mean to loop until cell A1 is empty.

'Select'ing in vba is rarely required and slows your code.

Turning screen updating off while your code runs will speed it up and cause a lot less 'action' on your screen.

Test this in a copy of your workbook.

Application.ScreenUpdating = False
Do
    If IsEmpty(Range("A1")) Then
        MsgBox ("completed")
        Exit Sub
    Else
        Workbooks.Open Filename:=(Range("E3") & Range("F3") & Range("A1"))
        
        Call macro_name
        
        Range("A1").Delete Shift:=xlUp
    End If
Loop
Application.ScreenUpdating = True

 

[JackDanIce]

If I understand:

- You want to open a file who's name is in A1
- Run a macro on it
- I assume the macro saves and closes that file
- Delete the contents of A1 and shift the rows up
- Open the (new) file name that is now in A1 and repeat above steps, until A1 is empty

Why not just work down column A until you reach an empty row, why the need to delete and shift up? Anyway, based on my above reading of what you want, try:

Application.ScreenUpdating = False
 
Do Until IsEmpty(Range("A1"))
        Workbooks.Open Filename:=(Range("E3") & Range("F3") & Range("A1"))
        Call macro_name
        Range("A1").Delete Shift:=xlUp 
Loop
 
MsgBox ("Completed")
 
Application.ScreenUpdating = True

As a pointer, you want to avoid using '.Select' or '.Activate' as much as possible in your code, it slows it down and can cause problems with more complex code.

 

[Peter_SSs]

In my code, "Exit Sub" would be better replaced with "Exit Do", as in Jack's code, to ensure screen updating is turned back on.

 

[olympiac]

The reason why I shift the cells up it for a user friendly purpose so that the user can see on the screen what has been completed.
However I would enrich your idea to go throu the list until we find an empty cell with the possibility to see what has been completed by adding a value in column B such as "completed"
A1 file_name_1 completed
A2 file_name_2 completed
A3 file_name_3 processing

Is it possible? do you know how to do that?

 

[JackDanIce]

Thank you for the compliment (acknowledgement?) Peter!

If you want to evaluate down the loop and provide an indicator in the adjacent cell in column B, try:

Dim i as Long
 
For i = 1 to Range("A" & Rows.Count).End(xlUp).Row
        Workbooks.Open Filename:=(Range("E3") & Range("F3") & Range("A" & i))
        Range("B" & i) = "Processing"
        Call macro_name
        Range("B" & i) = "Completed"
Next i
 
MsgBox ("Completed")

I agree with Peter, that you should turn the screen updating off; in my experience a user will always prefer a code to execute faster than have any fancy visual bells or whistles, but if you insist on column B showing "Processing" and then "Completed", I've removed the lines that control the screenupdating status.

I question, given how fast your machine runs and the macro you are calling executes, whether the user will be able to discerne the changing status of the rows in column B.

 

[olympiac]

I tried with the Application.ScreenUpdating but I still see the file opened going through the formatting. What's wrong?

 

[JackDanIce]

I'm guessing the macro you call after you open the file does not contain the code to turn on/off the screenupdating...

 

[olympiac]

I double checked, I have got the screenupdating in the macro that I call.

There must be something wrong somewhere. I need to check