1
I was building a simple listing, and came across the 'Runtime Error 13. Incompatible Types'. When I Clear it tells me that the error is in the two lines of code of my 'If', referring to the dates.
What happens is that I have a listing, a search button and 4 filters to be used - two of them are Combobox ('input types' and 'status'), and two others are Textbox (expiration date' and 'end date'').
My intention is to be able to use the filters or not. But when I click 'search' it returns the error. If I enter only a date and search, error. Search with only one Combobox or both, error. If I do not select anything and search, error.
Can anyone help me? I am new with VB, I may be making a basic mistake and not have noticed. I thank you already.
Note: I came across people saying that my spreadsheet could be protected but I already checked and was not.
Edit: In the third paragraph I made an error but already corrected. In fact even inserting start date and end of maturity I have found error. None of the filters are working.
Private Sub BtnPesquisar_Click()
LISTAR_ENTRADAS
End Sub
Private Sub TextFim_Change()
'Formatação para datas
TextFim.MaxLength = 10
If Len(TextFim) = 2 Or Len(TextFim) = 5 Then
TextFim.Text = TextFim.Text & "/"
End If
End Sub
Private Sub TextFim_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii < Asc(0) Or KeyAscii > Asc(9) Then
KeyAscii = 0
End If
End Sub
Private Sub TextInicio_Change()
'Formatação para datas
TextInicio.MaxLength = 10
If Len(TextInicio) = 2 Or Len(TextInicio) = 5 Then
TextInicio.Text = TextInicio.Text & "/"
End If
End Sub
Private Sub TextInicio_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii < Asc(0) Or KeyAscii > Asc(9) Then
KeyAscii = 0
End If
End Sub
Private Sub UserForm_Initialize()
'Declaração dos itens no Select
ComboTipo.AddItem "Pagar"
ComboTipo.AddItem "Receber"
ComboStatus.AddItem "Realizado"
ComboStatus.AddItem "Não Realizado"
End Sub
Sub LISTAR_ENTRADAS()
'Declaração das variáveis
Dim item As ListItem
Dim i As Integer
ListViewEntradas.ColumnHeaders.Clear
ListViewEntradas.ListItems.Clear
ListViewEntradas.Gridlines = True
ListViewEntradas.View = lvwReport
ListViewEntradas.FullRowSelect = True
ListViewEntradas.ColumnHeaders.Add Text:="Data de Registro", Width:=60, Alignment:=0
ListViewEntradas.ColumnHeaders.Add Text:="Tipo", Width:=70, Alignment:=0
ListViewEntradas.ColumnHeaders.Add Text:="Valor", Width:=80, Alignment:=0
ListViewEntradas.ColumnHeaders.Add Text:="Categoria", Width:=60, Alignment:=0
ListViewEntradas.ColumnHeaders.Add Text:="Cliente/ Fornecedor", Width:=120, Alignment:=0
ListViewEntradas.ColumnHeaders.Add Text:="CPF/ CNPJ", Width:=100, Alignment:=0
ListViewEntradas.ColumnHeaders.Add Text:="Data de Pagamento", Width:=70, Alignment:=0
ListViewEntradas.ColumnHeaders.Add Text:="Status", Width:=60, Alignment:=0
ListViewEntradas.ColumnHeaders.Add Text:="Observação", Width:=100, Alignment:=0
For i = 2 To Planilha2.Range("A1000000").End(xlUp).Row
If (ComboTipo.Text = "" Or ComboTipo.Text = Planilha2.Range("b" & i)) And _
(ComboStatus.Text = "" Or ComboStatus.Text = Planilha2.Range("h" & i)) And _
TextInicio.Text <= CDate(Planilha2.Range("g" & i)) And _
TextFim.Text >= CDate(Planilha2.Range("g" & i)) Then
'And _
'TextInicio.Text <= CDate(Planilha2.Range("g" & i)) And _
'TextFim.Text >= CDate(Planilha2.Range("g" & i))
Set item = ListViewEntradas.ListItems.Add(Text:=Planilha2.Range("a" & i))
item.SubItems(1) = Planilha2.Range("b" & i)
item.SubItems(2) = Planilha2.Range("c" & i)
item.SubItems(3) = Planilha2.Range("d" & i)
item.SubItems(4) = Planilha2.Range("e" & i)
item.SubItems(5) = Planilha2.Range("f" & i)
item.SubItems(6) = Planilha2.Range("g" & i)
item.SubItems(7) = Planilha2.Range("h" & i)
item.SubItems(8) = Planilha2.Range("i" & i)
End If
Next
End Sub
FWIW: Look at the data types, you are declaring i as
Integer
which is an old data type and used in many tutorials, but has only 16 bits and after 65 535 occurs overflow. Declare as 32-bit Long withDim i As Long
.– danieltakeshi
Create a [mcve] with some dummy data to reproduce this error and see https://pt.meta.stackoverflow.com/q/8388/75104
– danieltakeshi
Try to convert the date to a double number with
CDbl()
, because it is better to perform mathematical operations. For example:CDbl(TextInicio.Text) <= CDbl(Planilha2.Range("g" & i))
. And it is important to check if the data entered in Textoinicio is a date, the Isdate can be used.– danieltakeshi
It worked here, thanks for your help.
– Sena