Do Loop stops on formula error

[Rudi]

Hi,

I have code looping through records until it hits a blank cell. The problem is that it debugs if the activecell is an error (generated from a formula). How can I instruct it to continue looping irrespective of the content of the activecell...until it reaches a blank?

I thought of ignoring the error, but I do not want it to ignore errors inside the loop. If I use on error goto 0 just after the Do until line, then the next time it jumps into the loop, the code debugs again as the error handling is switched back on again!!

Any ideas to modify the code to loop until a blank cell? TX

SP2-Wed,21-8.jpg

[HansV]

Perhaps like this:

Code: Select all

    Do
        If Not IsError(ActiveCell) Then
            If ActiveCell = "" Then Exit Do
        End If
        ...
        ActiveCell.Offset(1).Select
    Loop

(I'd avoid using ActiveCell, but you already know that)

[Rudi]

I don't actually want it to exit the loop. I would like it to continue processing even if the cell is an error and only exit at a blank?

Also, what is the reason for avoiding activecell? I have traditionally always used activecell in a Do Loop?

TX

[Rudi]

Using your suggestion helps a bit, but now it errors right at the end...
Attached is W/B and code.

TX for assistance.

[HansV]

Can you explain in words what the code is supposed to do?

[Rudi]

Sure...

The code needs to email 3 attachments to the recipient. It would loop through the cell numbers and attach an invoice (pdf) and an item bill (pdf - in next row) and another doc (section commented out). Due to the unstructured rows (not always two rows per recipient, and sometimes not a recipient at all (errors in C column formula), the loop needs to check a few things:

1. the activecell (tel number) must match the one below
2. the activecell must not be an error
3. the activecell(offset(0,1) must not be an error (this cell has VLOOKUP to collect email of tel number owner - cannot send email if this is also error)

If above conditions all good, then email and mark in green and move 2 cells down to process next tel number
If any one of conditions above is false, then do not email, mark as red and move 1 cell down

Hope that clarifies things...

[HansV]

There will never be more than 2 rows per phone number?

[Rudi]

There will never be more than 2 rows per phone number?

No. Never more than 2.

[HansV]

Sorry about the delay - I had to do other things in between.

Code: Select all

Sub EmailFiles()
    Dim OutApp As Object, OutMail As Object, MyFileList(2) As String
    Dim i As Long, cel As Range

    ' We need to start Outlook only once
    Set OutApp = CreateObject("Outlook.Application")

    Set cel = Worksheets("Temp").Range("C1")
    Do
        If IsError(cel) Then
            ' Cell contains an error
            cel.Offset(0, 3).Interior.Color = vbRed
            cel.Offset(0, 3).Value = "Not Sent"
            Set cel = cel.Offset(1, 0)
        ElseIf cel = "" Then
            ' Cell is empty
            Exit Do
        ElseIf IsError(cel.Offset(1, 0)) Then
            ' Next cell contains an error
            cel.Offset(0, 3).Interior.Color = vbRed
            cel.Offset(0, 3).Value = "Not Sent"
            Set cel = cel.Offset(1, 0)
        ElseIf cel.Offset(1, 0) = cel Then
            ' Cell equal to next cell
            If Not IsError(cel.Offset(0, 1)) Then
                ' E-mail address available
                MyFileList(0) = cel.Offset(0, -2).Value
                MyFileList(1) = cel.Offset(1, -2).Value
                Select Case cel.Offset(0, 2).Value
                    Case 350
                        MyFileList(2) = strFolderPath & "\Letters\" & "350.doc"
                    Case 510
                        MyFileList(2) = strFolderPath & "\Letters\" & "510.doc"
                    Case Else
                        MyFileList(2) = strFolderPath & "\Letters\" & "Dealer.docx"
                End Select
                Set OutMail = OutApp.CreateItem(0)
                With OutMail
                    .To = cel.Offset(0, 1).Value   '"MyEmail@123.com"
                    .Subject = "Cellphone invoice and itemised billing"
                    .Body = "Attached is this months invoice details and forms."
                    For i = LBound(MyFileList) To UBound(MyFileList)
                        .Attachments.Add MyFileList(i)
                    Next i
                    .Display   'or replace with .Send
                End With
                cel.Offset(0, 3).Interior.Color = vbGreen
                cel.Offset(1, 3).Interior.Color = vbGreen
                cel.Offset(0, 3).Value = "Sent"
            Else
                ' E-mail address not available
                cel.Offset(0, 3).Interior.Color = vbRed
                cel.Offset(1, 3).Interior.Color = vbRed
                cel.Offset(0, 3).Value = "Not Sent"
            End If
            Set cel = cel.Offset(2, 0)
        Else
            ' Cell not equal to next cell
            cel.Offset(0, 3).Interior.Color = vbRed
            cel.Offset(0, 3).Value = "Not Sent"
            Set cel = cel.Offset(1, 0)
        End If
    Loop
End Sub

Note that the code doesn't select the Temp sheet, nor does it select cells.

[Rudi]

Hi,

No need to apologise for delay....this is a huge favour you show.

During my wait I sort of resolved it using an additional macro to first delete the #Value errors in column C before the loop processes and emails the data. HOWEVER, looking at the code you so kindly supplied, I have quickly replaced my resolution with yours as it does more what I intended it to originally do and it is obviously much more optimised. TX so much!!!

Though I know about ideally not moving cells and ranges in the sheet, I have always used activecell with a Do Loop (specifically as a condition: Do Until activecell = something). I see how you have simply said Do... and in the body of the loop you reference the necessary cells to process. Thats a new approach for me...but something i can adopt. It just makes stepping through the code more difficult now as you cannot see the actions on the screen (so I now need to rely on the Locals Window).

Thanks again.
As always, your contribution is HIGHLY valued.

Have a great day further....