Expected: end of statement in Macro

Hi all -

I have a macro that is supposed to communicate with Bloomberg to retrieve some static info from certain fields. The macro looks through my excel sheet that has columns A & B (Cusip and AbsRefID, respectively), and basically concatenates it into cell C so that Bloomberg can read it. Then I retrieve data points using Bloomberg's BDP function for each column. I'm getting an expected end of statement error in the highlighted portion of the code below. Any help is greatly appreciated.

Sub Format_1st_Run()
 
    For Each ws In Sheets
    ws.Activate
    Dim A As Long, B As Long
    A = Range("B" & Rows.Count).End(xlUp).Row
    For B = A To 1 Step -1
    
    'Define Security Type so BB can read
    If Range("B" & B).Value Like "AGY*" Then Range("C" & B).Value = Range("A" & B).Value & " Corp"
    If Range("B" & B).Value Like "CD*" Then Range("C" & B).Value = Range("A" & B).Value & " Corp"
    If Range("B" & B).Value Like "CORP*" Then Range("C" & B).Value = Range("A" & B).Value & " Corp"
    If Range("B" & B).Value Like "MUNI*" Then Range("C" & B).Value = Range("A" & B).Value & " Muni"
    If Range("B" & B).Value Like "PREF*" Then Range("C" & B).Value = Range("A" & B).Value & " Pfd"
    If Range("B" & B).Value Like "PRST*" Then Range("C" & B).Value = Range("A" & B).Value & " Pfd"
    If Range("B" & B).Value Like "FOREIGN*" Then Range("C" & B).Value = Range("A" & B).Value & " Corp"
    
    'Look up BB Data Points
    If Range("C" & B).Value Like "*Corp" Then Range("D" & B).Formula = "=BDP(Range("C" & B).Value, ""CALLED_DT"")"
        If Range("C" & B).Value Like "*Corp" Then Range("E" & B).Formula = "=BDP(Range("C" & B).Value, ""CALLED_PX"")"
            If Range("C" & B).Value Like "*Corp" Then Range("F" & B).Formula = "=BDP(Range("C" & B).Value, ""MOST_RECENT_REPORTED_FACTOR"")"
    If Range("C" & B).Value Like "*Muni" Then Range("D" & B).Formula = "=BDP(Range("C" & B).Value, ""MUNI_RECENT_REDEMP_DT"")"
        If Range("C" & B).Value Like "*Muni" Then Range("E" & B).Formula = "=BDP(Range("C" & B).Value, ""MUNI_RECENT_REDEMP_PX"")"
            If Range("C" & B).Value Like "*Muni" Then Range("F" & B).Formula = "=BDP(Range("C" & B).Value, ""MUNI_RECENT_REDEMP_TYP"")"
    If Range("C" & B).Value Like "*Pfd" Then Range("D" & B).Formula = "=BDP(Range("C" & B).Value, ""CALLED_DT"")"
        If Range("C" & B).Value Like "*Pfd" Then Range("E" & B).Formula = "=BDP(Range("C" & B).Value, ""CALLED_PX"")"    
    'Check to make sure that the cells are NOT requesting data
    
    'Delete bad data from Corp
    If Range("C" & B).Value Like "*Corp" And Range("F" & B).Value Like "1" Then Rows(B).Delete
    If Range("C" & B).Value Like "*Corp" And Range("F" & B).Value Like "0" Then Rows(B).Delete
    If Range("C" & B).Value Like "*Corp" And Range("D" & B).Value Like "N/A*" Then Rows(B).Delete
    If Range("C" & B).Value Like "*Corp" And Range("E" & B).Value Like "N/A*" Then Rows(B).Delete
    If Range("C" & B).Value Like "*Corp" And Range("F" & B).Value Like "N/A*" Then Rows(B).Delete
    If Range("C" & B).Value Like "*Corp" And Month(Range("D" & B)) <> Month(Date) Then Rows(B).Delete
     
    'Delete bad data from Muni
    If Range("C" & B).Value Like "*Muni" And Range("F" & B).Value <> "Called in Full" Then Rows(B).Delete
    If Range("C" & B).Value Like "*Muni" And Month(Range("D" & B)) <> Month(Date) Then Rows(B).Delete
    
    'Delete bad data from Pfd
    If Range("C" & B).Value Like "*Pfd" And Range("D" & B).Value Like "N/A*" Then Rows(B).Delete
    If Range("C" & B).Value Like "*Pfd" And Range("E" & B).Value Like "N/A*" Then Rows(B).Delete
    If Range("C" & B).Value Like "*Pfd" And Month(Range("D" & B)) <> Month(Date) Then Rows(B).Delete
    
    'For Corp, put any security with a factor other than 0 or 1 into a new worksheet
    'Verify that factor is in current month
    
    Next B
    Next ws
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
End Sub

Hi, reltub,

when I paste your code into a module the lines with the formulas to be inserted are highlighted.

You would need to double up the quotes there from (just the first line here):

If Range("C" & B).Value Like "*Corp" Then Range("D" & B).Formula = "=BDP(Range("C" & B).Value, ""CALLED_DT"")"

to

If Range("C" & B).Value Like "*Corp" Then Range("D" & B).Formula = "=BDP(Range(""C"" & B).Value, ""CALLED_DT"")"

Ciao,
Holger

Hi, reltub,

I must excuse - it was way to early this morning to answer.

"=BDP(" & Range("C" & B).Value & ", ""CALLED_DT"")"

for the Value of the cell and

"=BDP(" & Range("C" & B).Address & ", ""CALLED_DT"")"

for the address with absolute reference.

Ciao,
Holger

Hi, reltub,

could you please attach a workbook with some sample data for a check? That would life a lot easier at least for me and especially at this time of the day.

Ciao,
Holger

Hi, reltub,

as I donīt have access to Blomberg itīs hard to tell. But weīll try anyway. If you compare such a lot of lines to maybe be deleted Iīll try a slightly different setup as you have it - if your first check proves true the respective row is deleted but all further checks will be run as well.

Letīs take the first part like

    'Delete bad data from Corp
    If rng3.Value Like "*Corp" And rng6.Value Like "1" Then Rows(B).Delete
    If rng3.Value Like "*Corp" And rng6.Value Like "0" Then Rows(B).Delete
    If rng3.Value Like "*Corp" And rng4.Value Like "#N/A*" Then Rows(B).Delete
    If rng3.Value Like "*Corp" And rng5.Value Like "#N/A*" Then Rows(B).Delete
    If rng3.Value Like "*Corp" And rng6.Value Like "#N/A*" Then Rows(B).Delete
    If rng3.Value Like "*Corp" And rng4.Value <> Month(Date) Then Rows(B).Delete
    'Getting error:Data type mismatch above

Maybe you try to alter it to

        '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(B).Delete
            Exit For
          End If
        End If
        'Getting error:Data type mismatch above

I donīt know if that will help with the problem you encountered but when a criteria is met and the row is deleted the code will continue on the next row and not fulfill further checks for a row that maybe have been done before as well.

Ciao,
Holger

Hi, reltub,

you had a code which loops from the last line to the top. Insode that code thereīs a passge I cited which maybe could be replaced by the code I indicated. And if that works you could replace the other parts as well in the very same way.

Ciao,
Holger

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

Hi, reltub,

now that was all my fault because of teh Exit For.

Letīs try it with a slightly modified version:

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
    Dim blnSkip As Boolean
    
    For Each ws In Sheets
      ws.Activate
      lngLast = Range("B" & Rows.Count).End(xlUp).Row
      For lngRow = lngLast To 1 Step -1
        
        blnSkip = False
        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
            blnSkip = True
          End If
        End If
        'Getting error:Data type mismatch above
         
        'Delete bad data from Muni
        If Not blnSkip Then
          If rng3.Value Like "*Muni" Then
            If rng6.Value <> "CALLED IN FULL" Or rng4.Value <> Month(Date) Then
              Rows(lngRow).Delete
              blnSkip = True
            End If
          End If
        End If
        
        'Delete bad data from Pfd
        If Not blnSkip Then
          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
            End If
          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

Hi, reltub,

another option would be to put Application.Calculation = xlCalculationManual before the code and Application.Calculation = xlCalculationAutomatic at the end - by this calculation is only done once after the macro has finished deleting.

Ciao,
Holger

Hi, reltub,

I mentioned earlier on that I donīt have access to Blomberg so I donīt know whatīs the value of rng4.Value. I know for sure that Month(Date) will deliver 10 (a digit) for this month. If itīs a date in rng4 you may use Month(rng4.Value) but maybe itīs a string/text thatīs inside that cell.

Let me give you a small example on that:

Sub Sample()

Dim varMonth As Variant

varMonth = "10"

If varMonth = Month(Date) Then
  Debug.Print "recognised Month as String"
End If
If varMonth * 1 = Month(Date) Then
  Debug.Print "recognised Month when multiplied"
End If
If CLng(varMonth) = Month(Date) Then
  Debug.Print "recognised Month when converted"
End If

varMonth = 10

If varMonth = Month(Date) Then
  Debug.Print "fitting"
End If

End Sub

Please indicate whatīs the content of a rng4.Value if this wonīt help.

Ciao,
Holger

Hi, reltub,

I hve to copy the line from this articel and paste it into the workbook you supplied. Although I use a different country setting as you the date is recognized as this by my system and thus the line is deleted. Right now I ran out of ideas of how to help solving this problem. Letīs see what this weekend brings (except extended coding ).

Ciao,
Holger