VBA AdvancedFilter error

paroduso

Board Regular
Joined
Sep 11, 2013
Messages
77
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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
 

Attachments

  • 1.jpg
    1.jpg
    134.8 KB · Views: 2
  • 2.jpg
    2.jpg
    137.2 KB · Views: 3
  • 3.jpg
    3.jpg
    141.4 KB · Views: 3
  • 4.jpg
    4.jpg
    159.1 KB · Views: 3
Last edited by a moderator:

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,272
Office Version
  1. 365
Platform
  1. Windows
Shouldn't it be linha here rather than lin.
VBA Code:
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

Board Regular
Joined
Sep 11, 2013
Messages
77
Office Version
  1. 2019
Platform
  1. Windows
full code

VBA 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
VBA 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
 
Last edited by a moderator:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,272
Office Version
  1. 365
Platform
  1. Windows
Have you changed lin to linha as I suggested?
 

paroduso

Board Regular
Joined
Sep 11, 2013
Messages
77
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Have you changed lin to linha as I suggested?
hi. Norie..yes...and excel goes down everytime
 

Attachments

  • 5.jpg
    5.jpg
    253.8 KB · Views: 2

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,272
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 11, 2013
Messages
77
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,720
Messages
5,626,475
Members
416,187
Latest member
L_D18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top