Excel keeps jumping back to the top of my macro

P

Peterfallen

New Member
Joined
Jun 16, 2015
Messages
11
Hello,
I have a few macros that have started doing something weird.
The line Selection.Delete.Shift=xlToLeft, seems to end my macro. Any further F8 presses restarts the macro from the top - second time around the macro does complete all commands, but by then my data is corrupted.

Clearly I am doing something wrong. Perhaps it has something to do with where my cursor is?


VBA Code: 
Sub Referrals()
'
' Referrals Macro
'
Dim BRANCHCODE As Range
Dim lastrow As Long
'


    Range( _
        "A:B,D:I,K:L,N:AA,AC:Az,AB:AB,BB:BG,BH:BP").Select

    Selection.Delete Shift:=xlToLeft


*****************************************************************************************************************************************************************
[WHEN USING f8, MY MACRO STOPS COMPLETELY AS IF IT IS DONE. wHEN i PRESS F8 ONE MORE TIME, IT RETURNS TO THE START AND GOES AGAIN]
*****************************************************************************************************************************************************************


    Range("a:D").Select
   


    Cells.Select
    Cells.EntireColumn.AutoFit

    '   change names of branches
   
   
    lastrow = Cells(Rows.count, 4).End(xlUp).Row
    Set dataset = Range("D1:D" & lastrow)

    For Each cell In dataset

    If cell.Value = "ANA" Then
    cell.Value = "CHI"
    ElseIf cell.Value = "WEA" Then
    cell.Value = "WTO"
    ElseIf cell.Value = "POT" Then
    cell.Value = "MCA"
    ElseIf cell.Value = "MAN" Then
    cell.Value = "LAW"


    Else

    End If

        Next
   

   
   
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Branch Co"
   
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "ADA"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "ARD"
    Range("H4").Select
    ActiveCell.FormulaR1C1 = "ATO"
    Range("H5").Select
    ActiveCell.FormulaR1C1 = "CHI"
    Range("H6").Select
    ActiveCell.FormulaR1C1 = "DAV"
    Range("H7").Select
    ActiveCell.FormulaR1C1 = "DUR"
    Range("H8").Select
    ActiveCell.FormulaR1C1 = "ENI"
    Range("H9").Select
    ActiveCell.FormulaR1C1 = "HEN"
    Range("H10").Select
    ActiveCell.FormulaR1C1 = "HOL"
    Range("H11").Select
    ActiveCell.FormulaR1C1 = "HUG"
    Range("H12").Select
    ActiveCell.FormulaR1C1 = "IDA"
    Range("H13").Select
    ActiveCell.FormulaR1C1 = "IND"
    Range("H14").Select
    ActiveCell.FormulaR1C1 = "JOP"
    Range("H15").Select
    ActiveCell.FormulaR1C1 = "LAW"
    Range("H16").Select
    ActiveCell.FormulaR1C1 = "MCA"
    Range("H17").Select
    ActiveCell.FormulaR1C1 = "NRM"
    Range("H18").Select
    ActiveCell.FormulaR1C1 = "OKC"
    Range("H19").Select
    ActiveCell.FormulaR1C1 = "PON"
    Range("H20").Select
    ActiveCell.FormulaR1C1 = "PTU"
    Range("H21").Select
    ActiveCell.FormulaR1C1 = "PUR"
    Range("H22").Select
    ActiveCell.FormulaR1C1 = "SAL"
    Range("H23").Select
    ActiveCell.FormulaR1C1 = "SEM"
    Range("H24").Select
    ActiveCell.FormulaR1C1 = "SFD"
    Range("H25").Select
    ActiveCell.FormulaR1C1 = "SHA"
    Range("H26").Select
    ActiveCell.FormulaR1C1 = "SMI"
    Range("H27").Select
    ActiveCell.FormulaR1C1 = "STG"
    Range("H28").Select
    ActiveCell.FormulaR1C1 = "STL"
    Range("H29").Select
    ActiveCell.FormulaR1C1 = "TAL"
    Range("H30").Select
    ActiveCell.FormulaR1C1 = "TUL"
    Range("H31").Select
    ActiveCell.FormulaR1C1 = "WTO"
    Range("H32").Select
    ActiveCell.FormulaR1C1 = "WIC"
    Range("H33").Select
    ActiveCell.FormulaR1C1 = "WOO"

    Range("G5").Select
    ActiveCell.FormulaR1C1 = "& ANA"
    Range("G16").Select
    ActiveCell.FormulaR1C1 = "& POT"
    Range("G15").Select
    ActiveCell.FormulaR1C1 = "& MAN"
    Range("G33").Select
    ActiveCell.FormulaR1C1 = "& WEA"
   
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "1/1/20"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "2/1/20"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "3/1/20"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "4/1/20"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "5/1/20"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "6/1/20"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "7/1/20"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "8/1/20"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "9/1/20"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "10/1/20"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "11/1/20"
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "12/1/20"
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "1/1/21"
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "2/1/21"
    Range("W1").Select
    ActiveCell.FormulaR1C1 = "3/1/21"
    Range("X1").Select
    ActiveCell.FormulaR1C1 = "4/1/21"
    Range("Y1").Select
    ActiveCell.FormulaR1C1 = "5/1/21"
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = "6/1/21"
   
   
   
   
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = "TOTAL"
   
   
         Range("I1:Z1").Select
    Selection.NumberFormat = "[$-409]mmm-yy;@"
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
   
   
   
   
    ' formula for counting medicares
   
    Range("I2").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(C2,""Medicare"",C4,RC8,C3,""<""&R1C[1],C3,"">=""&R1C)"
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I33")
    Range("I2:I33").Select
    Selection.AutoFill Destination:=Range("I2:Z33"), Type:=xlFillDefault

   



   
   
   
    Range("AA2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-20]:RC[-1])"
    Selection.AutoFill Destination:=Range("AA2:AA33"), Type:=xlFillDefault
   
    Range("H36").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-34]C:R[-2]C)"
    Selection.AutoFill Destination:=Range("H36:z36"), Type:=xlFillDefault
   
   
   
   
   


    Range("AA35").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-33]C:R[-1]C)"
    Range("AA36").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-20]:RC[-1])"
    Range("AA37").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C2,""Medicare"")"
   





       
   
        Range("H1:Z1").Select
    Selection.NumberFormat = "[$-409]mmm-yy;@"
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
   

End Sub
 
Last edited by a moderator:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
How are you triggering this code? Also, do you have any event code in the worksheet, like a Worksheet_Change event?
 
Upvote 0
RoryA said:
How are you triggering this code? Also, do you have any event code in the worksheet, like a Worksheet_Change
Click to expand...

When I run it through a macro button on my ribbon, it sometimes completes the whole macro, and sometimes stops.
When I load up VB Editor and F8 through the code, it always stops at that point. Jumping back to the top if I keep pressing.

I do not have any event code.
 
Upvote 0
Do you have any conditional formatting in the sheet?
 
Upvote 0
I can't see why it would do what you describe, but I think you can simplify that part to just:

Code: 
Range("A:B,D:I,K:L,N:AZ,BB:BP").Delete Shift:=xlToLeft
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
149
Coyotex3
C
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
931
Joe4
Joe4
VinceF
  • Solved
Runtime Error 1004 with Macro
Replies
3
Views
164
Joe4
Joe4
C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
199
Coco1
C
M
  • Question
IF search is false, then delete entire row
Replies
3
Views
342
DanteAmor
DanteAmor

Forum statistics

Threads
1,214,643
Messages
6,120,702
Members
448,980
Latest member
CarlosWin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top