same macro, same data, but macro only works ion one sheet.

[JeffGrant]

Hi,

I have this macro

Sub Macro1()

LR = Cells(Rows.Count, 2).End(xlUp).Row

With Range("J4:J" & LR)
.FormulaR1C1 = _
        "=IF(RC[-5]*2-MINUTE(RC[-5]*120)/24/60/60<R[-1]C[-5]*2-MINUTE(R[-1]C[-5]*120)/24/60/60,1,"""")"
.Value = .Value
.SpecialCells(xlCellTypeConstants, 1).Select
.Clear
End With
Selection.EntireRow.Insert
End Sub

Which works like a treat in one workbook with one sheet. The macro is placed in a module. However, when the macro is transferred to another workbook, the Selection.EntireRow.Insert line goes to into an endless loop and requires a manual break to abort it.

In the first workbook, the process takes less than 2 seconds to complete on 3000 + records.
In the second workbook, where there are many sheets and any many module, the Selection.EntireRow.Insert goes into an endless loop - i think.......
The data is located in the same columns and rows. The sheets are named exactly the same.

Any guidance would be most appreciated.

Thanks in advance.

 

[Michael M]

Is there a worksheet_event in the workbook that causes issues ?
HAve a look in the Sheet modules

 

[Peter_SSs]

As well as looking in the individual sheet modules as suggested by Michael, look in the ThisWorkbook module of the problem workbook as event-driven code could also be there.

 

[JeffGrant]

Thank you for your prompt replies.

Unfortunately, the problem is in neither of these two spots. Please see that attached images.

 

[Michael M]

Another thing to check...
If you step through the code on the offending workbook, when you get to the line

With Range("J4:J" & LR)

What does LR return ??
AND
If you press CTRL + End, where does the cursor go ??

 

[JeffGrant]

Thanks Michael.

CTRL + End & LR both return the same row number. Which is the last row of the data set.

As we speak, I have been systematically deleting Modules, Forms and Sheets to see what causes the error. But then when I think I find I have found the culprit, I cant reproduce the error.

But the error seems to be created by a formula(s) in a sheet rather than in VBA code or user forms.

Rather mystifying....

 

[JeffGrant]

An Update....

I have worked out that there is a module that when deleted, this code works fine. When the module is there, Excel stops responding. Unfortunately, it happens to be the module with the largest amount of code.

This code below is located in it own module and does not reference any other sub, sheet, user form, variable other than the sheet that the code directly applies to. So why would some other code, in a completely different location and is completely unrelated to this cause such a drama?

Any guidance is much appreciated.

Sub Macro1()
Sheets("FedClass").Select
LR = Cells(Rows.Count, 2).End(xlUp).Row

With Range("J4:J" & LR)
.FormulaR1C1 = _
        "=IF(RC[-5]*2-MINUTE(RC[-5]*120)/24/60/60<R[-1]C[-5]*2-MINUTE(R[-1]C[-5]*120)/24/60/60,1,"""")"
.Value = .Value
.SpecialCells(xlCellTypeConstants, 1).Select
.Clear
End With
Selection.EntireRow.Insert
End Sub

 

[Michael M]

Isnt this the same code as posted originally??
What is in the module that is causing the problem?

 

[Michael M]

Unfortunately, it happens to be the module with the largest amount of code.

So, how about posting the ALL code from that offending module ??

 

[JeffGrant]

Hi Michael, I cant post the code without posting the data. Unfortunately, too sensitive for that I'm afraid.

At this point, I will leave the issues as unresolved because the way this particular part works in my model is not repeated very often

My solution will be to copy the data to another work book and run the code from there when required.

Thanks for your help.