VBA error "can not execute in break mode" when paste

[meakashgfx]

Dear all,

I am trying to paste a formula in a range it says "can not execute in break mode". Please take a look into my code below.

Dim lngMyRow As Long
    Dim lngStartRow As Long
    Dim lngLastRow As Long
    Dim rngMyRange As Range
    
    Application.ScreenUpdating = False
    
    lngLastRow = Cells(Rows.Count, "C").End(xlUp).Row
    
    For lngMyRow = 1 To lngLastRow
        If Len(Range("C" & lngMyRow)) > 0 Then
            lngStartRow = lngMyRow
            Exit For
        End If
    Next lngMyRow
    
    Set rngMyRange = Range("D" & lngStartRow & ":D" & lngLastRow)
    
    Range("D2,E2").Copy
    Range(rngMyRange.Address).Select
    Selection.PasteSpecial
    
    
    Range("B:B,C:C").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    
    'MsgBox rngMyRange.Address
        
    Application.ScreenUpdating = True

Thanks in advance!

 

[sijpie]

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> MacroYours()<br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' Commented to show some inefficiencies</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lngMyRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lngStartRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lngLastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rngMyRange <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <br>    lngLastRow = Cells(Rows.Count, "C").End(xlUp).Row<br>    <br>    <SPAN style="color:#00007F">For</SPAN> lngMyRow = 1 <SPAN style="color:#00007F">To</SPAN> lngLastRow<br>        <SPAN style="color:#007F00">' this loops through cells on the sheet. Assuming that _<br>          there will only very few rows before a value > 0 is _<br>          found this is OK. Else it is far quicker to load the _<br>          range into an array and check there. Reason: Every _<br>          read or write from/to sheet is slow.</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Len(Range("C" & lngMyRow)) > 0 <SPAN style="color:#00007F">Then</SPAN><br>            lngStartRow = lngMyRow<br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> lngMyRow<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rngMyRange = Range("D" & lngStartRow & ":D" & lngLastRow)<br>    <br>    <SPAN style="color:#007F00">' copy / paste is a very slow process as it goes via _<br>      the clipboard. Far quicker is to set the value of _<br>      the one range to the value off the other</SPAN><br>    Range("D2,E2").Copy<br>    <SPAN style="color:#007F00">' Range(rngMyRange.Address).Select is the same as _<br>      rngMyRange.select, but the 2nd is more efficient</SPAN><br>    Range(rngMyRange.Address).Select<br>    Selection.PasteSpecial<br>    <br>    <SPAN style="color:#007F00">' You do not need to .Select a range (or a sheet) to _<br>      do something with the range (or sheet). Just delete _<br>      the .Select up to the Selection</SPAN><br>    Range("B:B,C:C").Select<br>    Selection.Delete Shift:=xlToLeft<br>    Range("A1").Select<br>    <br>    <SPAN style="color:#007F00">'MsgBox rngMyRange.Address</SPAN><br>        <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> Macro_Improved()<br>    <SPAN style="color:#00007F">Dim</SPAN> lngStartRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lngLastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rngMyRange <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> vArr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <br>    lngLastRow = Cells(Rows.Count, "C").End(xlUp).Row<br>    vArr = Range("C1:C" & lngLastRow).Value<br>    <br>    <SPAN style="color:#00007F">For</SPAN> lngStartRow = 1 <SPAN style="color:#00007F">To</SPAN> lngLastRow<br>        <SPAN style="color:#007F00">' Find first value>0 in array.</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> vArr(lngMyRow, 1) > 0 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> lngStartRow<br>    <br>    <SPAN style="color:#007F00">' quick error check</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> lngStartRow > lngLastRow <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">' no value > 0 found</SPAN><br>        MsgBox "No value in column C > 0"<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rngMyRange = Range("D" & lngStartRow & ":D" & lngLastRow)<br>    <SPAN style="color:#007F00">' copy the values from D2:E2 in this range</SPAN><br>    rngMyRange.Value = Range("D2:E2").Value<br>    <SPAN style="color:#007F00">' Delete current B:C columns</SPAN><br>    Range("B1:C1").EntireColumn.Delete<br>        <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[meakashgfx]

Hi, thanks a lot for your hard work for me. actually I'm new in VBA. I tried your macro but I think you forgot to Dim lngMyRow, I am getting error in

[COLOR=#333333]"[/COLOR][COLOR=#00007F][FONT=Calibri]If[/FONT][/COLOR][COLOR=#333333][FONT=Calibri] vArr(lngMyRow, 1) > 0 [/FONT][/COLOR][COLOR=#00007F][FONT=Calibri]Then"[/FONT][/COLOR]

with the problem and what I wanted to do is, I'm sharing a snap here Screenshot by Lightshot

Thanks you very much again for you help. I really appreciate that.

 

[sijpie]

Sorry, forgot to replace that one with lngStartRow. So line:

        If vArr(lngMyRow, 1) > 0 Then

should be

        If vArr(lngStartRow, 1) > 0 Then