CODEWITHSUNDEEP
vbaexcel
paulinhax
paulinhax

Reputation: 602

excel: loop through column skipping values

Is there any way to skip a row inside a loop when it finds a certain value?

For example, I have a loop that runs through a column copying the date and pasting its value and adding one more day in another sheet using this code:

Sub Apoios()

Dim i As Long
Dim j As Long
Dim lastrow As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet

Set ws1 = Sheets("Plan3")
Set ws2 = Sheets("BASE_TOTAL")
Set ws3 = Sheets("APOIOS")

lastrow = ws2.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastrow
    For j = 1 To 11
        Select Case True
            Case IsEmpty(ws2.Cells(i, j)):
                ws3.Cells(i, j) = ""

            Case Not IsEmpty(ws2.Cells(i, j)):
                If ws1.Cells(4, 3) <= ws2.Cells(i, 8) And ws2.Cells(i, 5).Value <> "APOIO" Then
                    ws3.Cells(i, 1).Value = ws2.Cells(i, 1)
                    ws3.Cells(i, 2).Value = ws2.Cells(i, 2)
                    ws3.Cells(i, 3).Value = ws2.Cells(i, 3)
                    ws3.Cells(i, 4).Value = ws2.Cells(i, 4)
                    ws3.Cells(i, 5).Value = "APOIO"
                    ws3.Cells(i, 6).Value = "-"
                    ws3.Cells(i, 7).Value = DateAdd("d", 1, ws2.Cells(i, 8).Value)
                    ws3.Cells(i, 8).Value = "-------------"
                    ws3.Cells(i, 9).Value = ws2.Cells(i, 9)
                    ws3.Cells(i, 10).Value = ws2.Cells(i, 10)
                    ws3.Cells(i, 11).Value = ws2.Cells(i, 11)
                End If

        End Select
    Next j
Next i

End Sub

Although I have a Sub that runs through columns and write on cells "TECNICO NAO ENCONTRADO" if the person is not found in my database as you can see below.

enter image description here

As this value is not a date I get a:

Run-time error 13: type mismatch

At this line:

ws3.Cells(i, 7).Value = DateAdd("d", 1, ws2.Cells(i, 8).Value)

Is there any way to skip the cells that contain "TECNICO NAO ENCONTRADO" on it?

Upvotes: 2

Views: 389

Answers (1)

A.S.H
A.S.H

Reputation: 29332

I guess you mean to change this line

ws3.Cells(i, 7).Value = DateAdd("d", 1, ws2.Cells(i, 8).Value)

and similar one if you have some, into:

If IsDate(ws2.Cells(i, 8).Value) Then ws3.Cells(i, 7).Value = DateAdd("d", 1, ws2.Cells(i, 8).Value)

Upvotes: 2

Related Questions