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]

The code has got a problem...... the value "Processing" is appearing in column B in the file that is being manipulated.

Can you help to review the code


Application.ScreenUpdating = False
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 manipulate

Range("B" & i) = "Completed"
Next i

MsgBox ("Completed")
Application.ScreenUpdating = True
End Sub

 

[olympiac]

Solved!

I have inverted the following rows
Range("B" & i) = "Processing"
Workbooks.Open FileName:=(Range("E3") & Range("F3") & Range("A" & i))


Thanks

 

[JackDanIce]

Sorry missed that, wasn't paying attention as I tried to modify, my bad!

 

[Peter_SSs]

As far as dealing with the screen updating, you could consider removing that from the 'manipulate' code and controlling it from this code.

I am assuming that the 'manipulate' code is closing the workbook that this code is opening.

This should stop the user seeing the other workbook opening/closing etc but allow them to see the updating of column B in this workbook.

Note that this code also assumes that there are no blanks (or non-existent file names) within the list in column A.

The other thing is if the list in column A is fairly short (fits on the screen), then you probably don't need the final Message Box as the user would be able to see from column B that the last file has been 'Completed'.

If the list is longer, so that it doesn't fit on the screen, then the user would not see column B updating after a while unless we built a bit more in the code to ensure the sheet scrolled to where the user could see column B updating - and the eventual completion. See the extra bit in this code about ScrollRow. This will ensure row 1 is visible when the code starts then scroll the sheet if there is more than 10 files in column A.

Anyway, see if this adds anything of value. Your choice about the final Message Box as the user should always be able to see when the last value in column B is 'Completed'.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Process_Workbooks()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    ActiveWindow.ScrollRow = 1<br>    LR = Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> LR<br>        Range("B" & i).Value = "Processing"<br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        Workbooks.Open Filename:=(Range("E3") & Range("F3") & Range("A" & i))<br>        <SPAN style="color:#00007F">Call</SPAN> manipulate<br>        Range("B" & i).Value = "Completed"<br>        <SPAN style="color:#00007F">If</SPAN> i > 10 <SPAN style="color:#00007F">Then</SPAN><br>            ActiveWindow.ScrollRow = i - 9<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#007F00">'    MsgBox ("Completed")</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[olympiac]

Brilliant. Many thanks