Macro runs correctly, but only by running step by step (f8)

Hi there,

I have written a long macro whose purpose is to format tabs in a spreadsheet a certain way, count the number of entries in each spreadsheet and provide a summary on the first tab.

This macro works exactly the way I need it to when I go through it step by step (or even holding down f8), however when running the macro any other way, it seems that the macro skips a certain step of the code:



Dim MemChk As Range

Set MemChk = Worksheets(LpIndex).Rows("1:1").Find("Participating Member").Offset(1, 0)

While MemChk <> ""
If MemChk <> Left(ActiveWorkbook.Name, 3) Then
Cells(MemChk.Row, Worksheets(LpIndex).Rows("1:1").Find("field 1").Column).ClearContents
Cells(MemChk.Row, Worksheets(LpIndex).Rows("1:1").Find("field 2").Column).ClearContents
Cells(MemChk.Row, Worksheets(LpIndex).Rows("1:1").Find("field 3").Column).ClearContents
Cells(MemChk.Row, Worksheets(LpIndex).Rows("1:1").Find("field 4").Column).ClearContents
Cells(MemChk.Row, Worksheets(LpIndex).Rows("1:1").Find("field 5").Column).ClearContents
Cells(MemChk.Row, Worksheets(LpIndex).Rows("1:1").Find("field 6").Column).ClearContents
Cells(MemChk.Row, Worksheets(LpIndex).Rows("1:1").Find("field 7").Column).ClearContents
Set MemChk = MemChk.Offset(1, 0)
Else: Set MemChk = MemChk.Offset(1, 0)
End If
Wend


LpIndex is a reference to the current worksheet being worked on. As mutliple tabs need to be formatted, LpIndex changes when the next worksheet is being used. MemChk is simply a reference cell, when it is equal to the workbook name, it simply moves the reference cell down a row until that row is blank. If the cell value is not equal to the workbook name, then the macro needs to clear specific cells, whose field headers are referenced in the code.

When running through the code step by step, this section works as it should, but running the macro as a whole causes the macro not to clear the cells it should, and as such it appears as though the macro has skipped over the entire section, or possibly was unable to clear the contents correctly. The rest of the macro has no issues I can find.

Let me know if you need any additional information.