VBA AdvancedFilter error

[paroduso]

Hi everyone i need help...

i have a listtview witch is loaded with the database and 4 textboxes to work as filters...but when i press the textboxes...an error comes up...
thanks for the help.

the error comes up in this sub()

i have in the textboxes ivent change-call sub carregar

Sub CARREGAR()

ListView1.ListItems.Clear

Sheets("folha1").Select
linha = 5


Do Until Sheets("folha1").Cells(lin, 1) = ""

  
    If Cells(lin, 3).Row.Hidden = False Then
  
Set LI = ListView1.ListItems.Add(Text:=Sheets("FOLHA1").Cells(lin, 1).Value) 'ID
    LI = ListView1.ListItems.Add(Text:=Sheets("FOLHA1").Cells(lin, 2).Value) 'ARTTIGO
    LI = ListView1.ListItems.Add(Text:=Sheets("FOLHA1").Cells(lin, 3).Value) 'DESIGNAÇÃO
    LI = ListView1.ListItems.Add(Text:=Sheets("FOLHA1").Cells(lin, 4).Value) 'NORMA
    LI = ListView1.ListItems.Add(Text:=Sheets("FOLHA1").Cells(lin, 5).Value) 'EPM
    LI = ListView1.ListItems.Add(Text:=Sheets("FOLHA1").Cells(lin, 6).Value) 'SECÇÃO ALMA
  
    End If
  
    lin = lin + 1
  
    Loop
  
    Label1_registos = Me.ListView1.ListItems.Count

End Sub

 

[Norie]

Shouldn't it be linha here rather than lin.

Do Until Sheets("folha1").Cells(lin, 1) = ""

P.S. If you had Option Explicit at the top of the module this would have been caught as a compile error.

 

[paroduso]

full code

Private Sub ComboBox1_Change()

Folha1.Select

Folha1.Cells(2, 2) = Me.ComboBox1

Call CARREGAR

End Sub

Private Sub ComboBox2_Change()

Folha1.Select

Folha1.Cells(2, 3) = Me.ComboBox2

Call CARREGAR

End Sub

Private Sub ComboBox3_Change()

Folha1.Select

Folha1.Cells(2, 4) = Me.ComboBox3

Call CARREGAR

End Sub

Private Sub ComboBox4_Change()

Folha1.Select

Folha1.Cells(2, 5) = Me.ComboBox4

Call CARREGAR

End Sub

Private Sub CommandButton1_Click()

Call FILTRAR

End Sub

Private Sub CommandButton2_Click()

Call REMOVERFILTRAR

Folha1.Range("A2:O2") = Clear

ComboBox1 = Empty
ComboBox2 = Empty
ComboBox3 = Empty
ComboBox4 = Empty

End Sub

Private Sub UserForm_Initialize()

With ListView1

.Gridlines = True
.View = lvwReport
.FullRowSelect = True

.ColumnHeaders.Add Text:="ID", Width:=20, Alignment:=0
.ColumnHeaders.Add Text:="ARTIGO", Width:=50, Alignment:=2
.ColumnHeaders.Add Text:="DESIGNAÇÃO", Width:=150, Alignment:=2
.ColumnHeaders.Add Text:="NORMA", Width:=140, Alignment:=2


End With

Dim LInha, linhalist As Integer
Dim ultimalinha As Variant

linhalist = 0
LInha = 5

ListView1.ListItems.Clear

Folha1.Select

With Folha1


While .Cells(LInha, 2).Value <> ""

ultimalinha = Cells(Rows.Count, "A").End(xlUp).Row

With ListView1

Set LISTA = ListView1.ListItems.Add(Text:=Cells(LInha, "A").Value)

LISTA.ListSubItems.Add Text:=Cells(LInha, "B").Value
LISTA.ListSubItems.Add Text:=Cells(LInha, "C").Value
LISTA.ListSubItems.Add Text:=Cells(LInha, "D").Value


End With

linhalist = linhalist + 1

LInha = LInha + 1


Wend

End With

For X = 1 To ListView1.ListItems.Count
For Y = 1 To 4


Next Y
Next X

End Sub
Sub CARREGAR()

ListView1.ListItems.Clear

Sheets("folha1").Select
LInha = 5


Do Until Sheets("folha1").Cells(lin, 1) = ""

   
    If Cells(lin, 3).Row.Hidden = False Then
   
Set Li = ListView1.ListItems.Add(Text:=Sheets("FOLHA1").Cells(lin, 1).Value) 'ID
    Li = ListView1.ListItems.Add(Text:=Sheets("FOLHA1").Cells(lin, 2).Value) 'ARTTIGO
    Li = ListView1.ListItems.Add(Text:=Sheets("FOLHA1").Cells(lin, 3).Value) 'DESIGNAÇÃO
    Li = ListView1.ListItems.Add(Text:=Sheets("FOLHA1").Cells(lin, 4).Value) 'NORMA
    Li = ListView1.ListItems.Add(Text:=Sheets("FOLHA1").Cells(lin, 5).Value) 'EPM
    Li = ListView1.ListItems.Add(Text:=Sheets("FOLHA1").Cells(lin, 6).Value) 'SECÇÃO ALMA
   
    End If
   
    lin = lin + 1
   
    Loop
   
    Label1_registos = Me.ListView1.ListItems.Count

End Sub

and the module code

Sub FILTRAR()

    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Range("A4:E20").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("A1:E2"), Unique:=False
End Sub
Sub REMOVERFILTRAR()


On Error Resume Next

    ActiveSheet.ShowAllData
   
End Sub

 

[Norie]

Have you changed lin to linha as I suggested?

 

[paroduso]

Have you changed lin to linha as I suggested?

hi. Norie..yes...and excel goes down everytime

 

[Norie]

After you made the change are you getting any errors?

If you aren't have you tried stepping through the code to see what's causing the problem?

 

[paroduso]

After you made the change are you getting any errors?

If you aren't have you tried stepping through the code to see what's causing the problem?

now stops at this point

If Cells(linha, 1).Row.Hidden = False Then