Help with error handling inside a for loop

[Firesword]

Hi

I'm having trouble with an error handling section of code that fails sometimes, but I can't figure out why it.

I have a spreadsheet with values, and there will be occasions when to include the (value x qty) and when not to, I have a for loop checking each line, it will either list "strBuy" with a "Yes" or "No" this part of the code works fine.

The issue is when using worksheetfunction ".Cells(ch, "S") = Application.WorksheetFunction.Sum(.Cells(ch, "R") * .Cells(ch, "I"))", there will be times when there is no value for the price but text instead. This will cause the formula to error. What I'm looking for is to put "Unknow Price" when there is an error, or do the sum when it's fine and continue to the next line.

Using windows 11 64 bit, with office 2016.

This is the code that I'm having issues with

            If strBuy = "Yes" Then
                On Error GoTo PriceUnknownB
                .Cells(ch, "S") = Application.WorksheetFunction.Sum(.Cells(ch, "R") * .Cells(ch, "I"))
                GoTo PriceContinueB
PriceUnknownB:
                .Cells(ch, "S") = "Unknown Price"
PriceContinueB:
                On Error GoTo 0
            End If

This is the full code

    With Sheet5
        lLastRow = .Range("A" & Rows.Count).End(xlUp).Row
        
        For ch = 6 To lLastRow
            lLevel = .Cells(ch, "A")
            If lLevel = 1 Then l1 = .Cells(ch, "H")
            If lLevel = 2 Then l2 = .Cells(ch, "H")

            If lLevel = 1 Then
                If l1 = "B" Then
                    strBuy = "Yes"
                Else
                    strBuy = "No"
                End If
            End If
            
            If lLevel = 2 Then
                If l1 = "B" Then
                    strBuy = "No"
                ElseIf l2 = "B" Then
                    strBuy = "Yes"
                Else
                    strBuy = "No"
                End If
            End If

            If strBuy = "Yes" Then
                On Error GoTo PriceUnknownB
                .Cells(ch, "S") = Application.WorksheetFunction.Sum(.Cells(ch, "R") * .Cells(ch, "I"))
                GoTo PriceContinueB
PriceUnknownB:
                .Cells(ch, "S") = "Unknown Price"
PriceContinueB:
                On Error GoTo 0
            End If
        Next
        
    End With

Any help will be appreciated.

Thanks

Simon

 

[Jeffrey Mahoney]

Please consider this

    On Error Resume Next
    If IsError(.Cells(ch, "R") * .Cells(ch, "I")) Then
      .Cells(ch, "S").Value = "Unknown Price"
    Else
      .Cells(ch, "S").Value = (.Cells(ch, "R") * .Cells(ch, "I"))
    End If
    On Error GoTo 0

There didn't seem to be a reason to sum the multiplication of two numbers

 

[dmt32]

Hi
untested but see if this update resolves your issue

  Dim Price As Double
    With Sheet5
        lLastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        For ch = 6 To lLastRow
            
            'your code
            
            If strBuy = "Yes" Then
                Price = Val(.Cells(ch, "R")) * Val(.Cells(ch, "I"))
                .Cells(ch, "S") = IIf(Price > 0, Price, "Unknown Price")
            End If
        Next ch
        
    End With

Dave

 

[Firesword]

Please consider this

    On Error Resume Next
    If IsError(.Cells(ch, "R") * .Cells(ch, "I")) Then
      .Cells(ch, "S").Value = "Unknown Price"
    Else
      .Cells(ch, "S").Value = (.Cells(ch, "R") * .Cells(ch, "I"))
    End If
    On Error GoTo 0

There didn't seem to be a reason to sum the multiplication of two numbers

Pefect, thank you very much.