macro runs when stepped through but deletes rows of original table when run with f5

[Ziggs]

Hi all,

New to excel and can't figure out why this macro deletes some of my rows when it is run. Any help appreciated . My original data is across two sheets with first sheet having 19 columns with columns I to S housing numbers that are manipulated to calculate cash which is inputted in the 'dummy' lines.

Hope this is clear.

Code pasted below:

Sub dummybond()
 
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.PrintCommunication = True


Dim cash As Long
Dim total As Long
Dim Lastrow As Long
Dim dte As Double
Dim lastRepoRow As Variant
Dim del_row As Variant
Dim endrow As Long
Dim mult As Variant
Dim Myobject As Object




booknb = 0
namecell = Worksheets("Complete Radar").Range("start_name_sheet").Value
line_radar = 2


While Not IsEmpty(namecell)
    ccy = Worksheets("Complete Radar").Range("start_name_sheet").Offset(booknb, 1).Value 'Currency is euro
    Fromto = Worksheets("Complete Radar").Range("start_name_sheet").Offset(booknb, 2).Value 'otc identifies bond trade in radar
    Fwd_dt = Worksheets("Complete Radar").Range("start_name_sheet").Offset(booknb, 9).Value
    Book = Worksheets("Complete Radar").Range("start_name_sheet").Offset(booknb, 3).Value
    Cparty = Worksheets("Complete Radar").Range("start_name_sheet").Offset(booknb, 4).Value
    Internal = Worksheets("Complete Radar").Range("start_name_sheet").Offset(booknb, 5).Value
    Deal_Type = Worksheets("Complete Radar").Range("start_name_sheet").Offset(booknb, 6).Value
    SubType = Worksheets("Complete Radar").Range("start_name_sheet").Offset(booknb, 7).Value
    Contingent = Worksheets("Complete Radar").Range("start_name_sheet").Offset(booknb, 8).Value
    d_ccy = Worksheets("Complete Radar").Range("start_name_sheet").Offset(booknb, 10).Value
    sheet_name = namecell ' sheet name is EDMBLBEALO first
    
    i = 1
    
    While Worksheets(sheet_name).Cells(i, 1).Value <> "Fwd date" And i < 100
    
        i = i + 1
    
    Wend  ' loops through columns other than FWd date in first row of worksheet


    
    'look for CCY col
    col_ccy = 1
    While Worksheets(sheet_name).Cells(i, col_ccy).Value <> ccy And col_ccy < 100
    
    col_ccy = col_ccy + 1
    
     Wend
     
'     fwd_cash = Worksheets("complete radar").Range("fwd_cash").Columns
     
    'look for deal currecncy
    
    deal_ccy = 1
      While Worksheets(sheet_name).Cells(i, deal_ccy).Value <> d_ccy And deal_ccy < 100
    
    deal_ccy = deal_ccy + 1
    
     Wend
     
    'look for fwd_dt
    
    fwdt = 1
    While Worksheets(sheet_name).Cells(i, fwdt).Value <> Fwd_dt And fwdt < 100


    fwdt = fwdt + 1


    Wend


    
    'look for Book
    bk = 1
    While Worksheets(sheet_name).Cells(i, bk).Value <> Book And bk < 100


    bk = bk + 1


    Wend


    'look for counterparty


    cp = 1
    While Worksheets(sheet_name).Cells(i, cp).Value <> Cparty And cp < 100


    cp = cp + 1


    Wend


    'look for Internal
    inte = 1
    While Worksheets(sheet_name).Cells(i, inte).Value <> Internal And inte < 100


    inte = inte + 1


    Wend


    'look for Deal_Type
    dt = 1
    While Worksheets(sheet_name).Cells(i, dt).Value <> Deal_Type And dt < 100


    dt = dt + 1


    Wend


    'look for SubType
    st = 1
    While Worksheets(sheet_name).Cells(i, st).Value <> SubType And st < 100


    st = st + 1


    Wend


    'look for Contingent
    ctgent = 1
    While Worksheets(sheet_name).Cells(i, ctgent).Value <> Contingent And ctgent < 100


    ctgent = ctgent + 1


    Wend


'--------------------------------------------------------loops to find bond cashflows
'clear last row




lastRepoRow = Worksheets(sheet_name).Range("A:Z").Find(what:="Repo", after:=[F1], searchdirection:=xlPrevious).Row




del_row = lastRepoRow + 1


Rows(del_row).EntireRow.Delete




endrow = Worksheets(sheet_name).Range("A" & Rows.Count).End(xlUp).Row + 1


For m = 2 To lastRepoRow


mult = (Worksheets(sheet_name).Cells(m, col_ccy).Value)


total = 0




begcol = 9








If sheet_name = "EDMBLBAELO" Then endcol = Worksheets(sheet_name).Cells(1, Columns.Count).End(xlToLeft).Column + 4 Else endcol = Worksheets(sheet_name).Cells(1, Columns.Count).End(xlToLeft).Column + 3




For k = begcol To endcol


   If k <> col_ccy And Not IsEmpty(Worksheets(sheet_name).Cells(m, k).Value) Then
       total = total + Worksheets(sheet_name).Cells(m, k).Value
   
   End If




Next k






For k = begcol To endcol


If k <> col_ccy And IsNumeric(Worksheets(sheet_name).Cells(m, k).Value) And Worksheets(sheet_name).Cells(m, k).Value <> 0 And total <> 0 Then
       
        
          
    
       If IsEmpty(Worksheets(sheet_name).Cells(m, col_ccy)) Then Worksheets(sheet_name).Cells(m, col_ccy).Value = -total ''??
    
       If IsEmpty(Worksheets(sheet_name).Cells(m, col_ccy)) Then cash = total Else cash = -(Worksheets(sheet_name).Cells(m, k) * (mult / total))
       
       dte = Worksheets(sheet_name).Cells(m, fwdt).Value
       ccy = Worksheets(sheet_name).Cells(m, deal_ccy).Value
                 
        If cash <> 0 Then
        Worksheets(sheet_name).Cells(endrow, col_ccy).Value = cash
   
        Worksheets(sheet_name).Cells(endrow, deal_ccy) = ccy
        
        Worksheets(sheet_name).Cells(endrow, bk) = sheet_name


        Worksheets(sheet_name).Cells(endrow, cp) = "Dummy"
        
        Worksheets(sheet_name).Cells(endrow, inte) = "Y"
       
        Worksheets(sheet_name).Cells(endrow, dt) = "Dummy Bond"
      
        Worksheets(sheet_name).Cells(endrow, st) = "STANDARD"
        
        Worksheets(sheet_name).Cells(endrow, ctgent) = "N"
      
        Worksheets(sheet_name).Cells(endrow, fwdt).Value = dte
         
        Worksheets(sheet_name).Cells(endrow, fwdt).NumberFormat = "dd\/mm\/yyyy"
        
        
        
        End If
        endrow = endrow + 1
        
    




End If


Next k


Next m 


booknb = booknb + 1
namecell = Worksheets("Complete Radar").Range("start_name_sheet").Offset(booknb, 0).Value
Wend
 

End Sub

 

[The_Macrotect]

Hi Ziggs,

About 3/4 of the way down, your code looks like this:

'--------------------------------------------------------loops to find bond cashflows
'clear last row




lastRepoRow = Worksheets(sheet_name).Range("A:Z").Find(what:="Repo", after:=[F1], searchdirection:=xlPrevious).Row




del_row = lastRepoRow + 1


Rows(del_row).EntireRow.Delete

This last line appears to delete an entire row, and does so after looking for the word "Repo". Does this account for the rows you're missing?