Expected: end of statement in Macro

**reltub** · 10-18-2012, 01:04 PM

So the new code could be somethin like this:

Sub Step_2test()

    'This step of the Callable Bonds Download macro deletes the bad data from each Security Type
    'Cuts and pastes and security where Security Type is Corp and 0<Factor<1 into a new spreadsheet
    'Verify that this factor is for the current month; if not, delete entire row

    For Each ws In Sheets
    ws.Activate
 
    
    Set rng1 = Range("A1:A")
    Set rng2 = Range("B1:B")
    Set rng3 = Range("C1:C")
    Set rng4 = Range("D1:D")
    Set rng5 = Range("E1:E")
    Set rng6 = Range("F1:F")


    'Delete bad data from Corp
    If rng3.Address Like "*Corp" Then
          If rng6.Value Like "1" Or rng6.Value Like "0" Or rng6.Value Like "#N/A*" _
              Or rng4.Value Like "#N/A*" Or rng5.Value Like "#N/A*" Or rng4.Value <> Month(Date) Then
            Rows(B).Delete
            Exit For
          End If
        End If

    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True

End Sub

**HaHoBe** · 10-18-2012, 01:16 PM

Hi, reltub,

I think your compiler wouldn´t like that code sniplet (missing Next for both loops).

Maybe the code may look like this:

Sub Step_2()

    'This step of the Callable Bonds Download macro deletes the bad data from each Security Type
    'Cuts and pastes and security where Security Type is Corp and 0<Factor<1 into a new spreadsheet
    'Verify that this factor is for the current month; if not, delete entire row

    Dim ws As Worksheet
    Dim rng1 As Range, rng2 As Range, rng3 As Range
    Dim rng4 As Range, rng5 As Range, rng6 As Range
    Dim lngLast As Long, lngRow As Long
    
    For Each ws In Sheets
      ws.Activate
      lngLast = Range("B" & Rows.Count).End(xlUp).Row
      For lngRow = lngLast To 1 Step -1
      
        Set rng1 = Range("A" & lngRow)
        Set rng2 = Range("B" & lngRow)
        Set rng3 = Range("C" & lngRow)
        Set rng4 = Range("D" & lngRow)
        Set rng5 = Range("E" & lngRow)
        Set rng6 = Range("F" & lngRow)
    
        'Check to make sure that the cells are NOT requesting data
           
        'Delete bad data from Corp
        If rng3.Value Like "*Corp" Then
          If rng6.Value Like "1" Or rng6.Value Like "0" Or rng6.Value Like "#N/A*" _
              Or rng4.Value Like "#N/A*" Or rng5.Value Like "#N/A*" Or rng4.Value <> Month(Date) Then
            Rows(lngRow).Delete
            Exit For
          End If
        End If
        'Getting error:Data type mismatch above
         
        'Delete bad data from Muni
        If rng3.Value Like "*Muni" Then
          If rng6.Value <> "CALLED IN FULL" Or rng4.Value <> Month(Date) Then
            Rows(lngRow).Delete
            Exit For
          End If
        End If
        
        'Delete bad data from Pfd
        If rng3.Value Like "*Pfd" Then
          If rng4.Value Like "#N/A*" Or rng5.Value Like "#N/A*" Or rng4.Value <> Month(Date) Then
            Rows(lngRow).Delete
            Exit For
          End If
        End If
        
        'For Corp, put any security with a factor other than 0 or 1 into a new spreadsheet
        'Verify that factor is in current month
      
      Next lngRow
    Next ws
'    Application.ScreenUpdating = True
'    Application.DisplayStatusBar = True
    
End Sub

Ciao,
Holger