Error: VBA types do not match

Question

Error: VBA types do not match

Navigation

#1 by (3 votes)
#2 by (3 votes)

1

I'm doing a macro that compares two columns at the same time to perform an operation, column A is of type Integer and column C is of type Date, the problem is that when closing the cycle it throws me the error of <Se ha producido el error '13' en tiempo de ejecución: No coinciden los tipos> . It is worth mentioning that he does the whole operation except at the end. I appreciate any suggestions.

My code is as follows:

Sub ChecarHorarioComida()
    'Declaracion de varaibles
    Dim valor1 As Integer
    Dim valor2 As Integer

    Dim fecha1 As Date
    Dim fecha2 As Date


    Dim Row As Long
    Dim contador As Long
    Dim ws As Worksheet

    Set ws = ActiveSheet
    Row = 1
    contador = 0

    While ws.Cells(Row, 1).Value <> ""
        valor1 = ws.Cells(Row, 1).Value
        valor2 = ws.Cells(Row + 1, 1).Value

        fecha1 = Format(ws.Cells(Row, 3).Value, "Short Date")
        fecha2 = Format(ws.Cells(Row + 1, 3).Value, "Short Date")

        If valor1 = valor2 And fecha1 = fecha2 Then
            contador = contador + 1

        Else
            Select Case contador
                Case 0
                    Range("A" & Row, "E" & Row).Interior.Color = RGB(255, 96, 96)
                Case 1
                    Range("A" & Row, "E" & Row).Interior.Color = RGB(0, 204, 0)                   

            End Select
            contador = 0
        End If
        Row = Row + 1
    Wend

End Sub

vba macros excel

asked by Silvestre Silva 08.11.2017 в 05:47

source

2 answers

3

It is very likely that the problem will happen as you are iterating. You scroll the rows until the value of the cell in Row is different from "" or is empty ( While ws.Cells(Row, 1).Value <> "" ), the problem is that with the last row that if you have a value you are doing this logic:

fecha2 = Format(ws.Cells(Row + 1, 3).Value, "Short Date")

That is, you recover the Row + 1 cell, which obviously if Row points to the last row Row + 1 will be empty. Therefore you will have an error when using Format . If you go deeper into the logic of your algorithm, or else the cycle you do it like this:

While ws.Cells(Row + 1, 1).Value <> "")

Or you control the error of the last row by On error .

answered by 08.11.2017 в 14:51

Error trying to find the first letter and the first vowel How to make a pointer by reference in C

score 3 · Accepted Answer

This is my solution in case someone happens something similar ...

Sub ChecarHorarioComida()
On Error Resume Next 'Controlar error

'Declaracion de varaibles   
Dim valor1 As Integer
Dim valor2 As Integer
Dim fecha1 As Date
Dim fecha2 As Date
Dim Row As Long
Dim contador As Long
Dim ws As Worksheet

Set ws = ActiveSheet
Row = 1
contador = 0

While ws.Cells(Row, 1).Value <> ""
    valor1 = ws.Cells(Row, 1).Value
    valor2 = ws.Cells(Row + 1, 1).Value

    fecha1 = Format(ws.Cells(Row, 3).Value, "Short Date")
    fecha2 = Format(ws.Cells(Row + 1, 3).Value, "Short Date")

    If valor1 = valor2 And fecha1 = fecha2 Then
        contador = contador + 1
    Else
        Select Case contador
            Case 0
                Range("A" & Row, "E" & Row).Interior.Color = RGB(255, 96, 96)
            Case 1
                Range("A" & Row, "E" & Row).Interior.Color = RGB(0, 204, 0)
        End Select
        contador = 0
    End If
    Row = Row + 1
Wend