VBA Excel 2010, isError function

Question

I want the program to output a message if no error is found.

   Sub casesVsQueue()
                 Dim loop_counter As Integer
                 Dim colD_counter As Integer
                 loop_counter = 1
                 colD_counter = 2
      Do Until IsEmpty(Sheets("Sheet1").Range("A" & loop_counter).Value)
              If IsError(Sheets("Sheet1").Range("C" & loop_counter).Value) Then      
                    Sheets("Sheet1").Range("D" & colD_counter).Value = Sheets("Sheet1").Range("A" & loop_counter).Value
                    colD_counter = colD_counter + 1

             End If
             loop_counter = loop_counter + 1
      Loop
End Sub

So is there a problem with your code currently?

Matt Cremeens
– Matt Cremeens

2016-06-06 20:48:05 +00:00
Commented Jun 6, 2016 at 20:48 — Matt Cremeens
– Matt Cremeens, Commented Jun 6, 2016 at 20:48

EoinS · Accepted Answer · 2016-06-06 20:52:08Z

1

At the very end after your loop, before you close your sub add this:

      Loop
    If colD_counter = 2 Then MsgBox "Everything is fine"
End Sub

Basically because colD_counter 'counts' errors, if it doesn't change during your loop, stays = 2, then you have msgbox saying no error come up.

You can leave the rest of your vb untouched. I just tried it out and it seems to work fine.

answered Jun 6, 2016 at 20:52

EoinS

5,4901 gold badge21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ralph · Accepted Answer · 2016-06-06 20:58:43Z

0

Is this what you are looking for?

Sub casesVsQueue()

Dim loop_counter As Integer
Dim colD_counter As Integer

Dim lngLastRow As Long

loop_counter = 1
colD_counter = 2

With ThisWorkbook.Worksheets("Sheet1")
    lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For loop_counter = 1 To lngLastRow
        If VarType(.Cells(loop_counter, "C").Value) = vbError Or .Cells(loop_counter, "C").Value = vbNullString Then
            .Cells(colD_counter, "D").Value = .Cells(loop_counter, "A").Value
            colD_counter = colD_counter + 1
        End If
    Next loop_counter
End With

End Sub

Let me know if you have any problems or questions.

edited Jun 6, 2016 at 20:58

answered Jun 6, 2016 at 20:47

Ralph

9,4544 gold badges35 silver badges42 bronze badges

Collectives™ on Stack Overflow

VBA Excel 2010, isError function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related