Error handling loop vba

Hi all

I have a for loop as follows:

'initiate inner loop
For j = 1 To LastRowCashAcc

'Buy side checks (includes generic such as Tcodes and base currency)
If BUYcashStr = wbDeb.Worksheets("All Cash Accounts").Cells(j, 2) Then
'Check buy cash account currencies
BUYacc_ccy = wbDeb.Worksheets("All Cash Accounts").Cells(j, 4)
End If
Next j

When BUYcashStr does not appear in the wbDeb worksheet I obviously get an error. I need to handle this to skip to the next iteration i.e. Next j?

Many thanks

Jack

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Hi I have truncated the loop apologies - I think the error actually comes further down when trying to calculate pcTrans (type mismatch error)

 

'Initiate loop
For i = 12 To LastRowFX

    'Delete warnings and remove formatting
    Range(Cells(i, 15), Cells(i, 20)).ClearContents
    Range(Cells(i, 1), Cells(i, 15)).Font.Color = vbBlack
    Range(Cells(i, 15), Cells(i, 20)).Interior.Color = xlNone

    'Find location of first and second dash in the CIF (usually 5th and 13th digits)
    dash1 = WorksheetFunction.Find("-", Cells(i, 2))
    dash2 = WorksheetFunction.Find("-", Cells(i, 2), dash1 + 1)
    startCIF = Mid(Cells(i, 2), 1, dash2 - 1)
    
    'Add cash suffix to midcif to get BUY and SELL settle cash accounts
    buyStr = Cells(i, 5)
    sellStr = Cells(i, 8)
    BUYcashStr = startCIF & buyStr
    SELLcashStr = startCIF & sellStr
    
    'initiate inner loop
    For j = 1 To LastRowCashAcc
        
        'Buy side checks (includes generic such as Tcodes and base currency)
        If BUYcashStr = wbDeb.Worksheets("All Cash Accounts").Cells(j, 2) Then
            'Check buy cash account currencies
            BUYacc_ccy = wbDeb.Worksheets("All Cash Accounts").Cells(j, 4)
            'Check type of cash account
            BUYacc_type = wbDeb.Worksheets("All Cash Accounts").Cells(j, 3)
            'Check base currency
            Base_ccy = wbDeb.Worksheets("All Cash Accounts").Cells(j, 6)
            'Enter T-code
            Cells(i, 13) = wbDeb.Worksheets("All Cash Accounts").Cells(j, 8)
            
            'Check %P/F for buy transactions
            If Cells(i, 4) <> 0 Then
            QuoteCCY = Cells(i, 3)
                'Loop to find rate
                For ii = 1 To LastRowFXLkup
                    'Define buy ccy and buy multiplier
                    QuoteCCY_Lookup = wbDeb.Sheets("FX Rates").Cells(ii, 1)
                    FXrate = wbDeb.Sheets("FX Rates").Cells(ii, 2)
                    
                    'Get rate for ccy and store as varibale
                    If QuoteCCY = QuoteCCY_Lookup Then
                        USD_amt = wsFX.Cells(i, 4) * FXrate
                        pcTrans = USD_amt / wbDeb.Worksheets("All Cash Accounts").Cells(j, 7)
                    End If
                Next ii
            End If
            
            'Check %P/F for sell transactions
            If Cells(i, 4) = 0 Then
            QuoteCCY = Cells(i, 6)
                'Loop to find rate
                For ii = 1 To LastRowFXLkup
                    'Define buy ccy and buy multiplier
                    QuoteCCY_Lookup = wbDeb.Sheets("FX Rates").Cells(ii, 1)
                    FXrate = wbDeb.Sheets("FX Rates").Cells(ii, 2)
                    
                    'Get rate for ccy and store as varibale
                    If QuoteCCY = QuoteCCY_Lookup Then
                        USD_amt = wsFX.Cells(i, 7) * FXrate
                        pcTrans = USD_amt / wbDeb.Worksheets("All Cash Accounts").Cells(j, 7)
                    End If
                Next ii
            End If


        End If
        
        'Sell side checks
        If SELLcashStr = wbDeb.Worksheets("All Cash Accounts").Cells(j, 2) Then
            'Check buy cash account currencies
            SELLacc_ccy = wbDeb.Worksheets("All Cash Accounts").Cells(j, 4)
            'Check type of cash account
            SELLacc_type = wbDeb.Worksheets("All Cash Accounts").Cells(j, 3)
        End If
        
    Next j