PDA

View Full Version : [SOLVED] Object variable or With block variable not set -Run-Time error 91



djemy1975
11-11-2018, 02:27 AM
Dear friends,

I am going to adapt a userform to filter from three sheets ("BASIC","SPECIFIC" and "IMPROVE"),everything is running well except when adding another sheet ,I immediately get the error mentioned in the subject.Could some one please try to help me get rid of this problem.I believe that the problem is in declaring sheets ,but I could not get it set.

Herewith my sample data:

Best regards,

Paul_Hossler
11-11-2018, 08:51 AM
There were some data link errors in the attachment that I skipped


Based on what I could test otherwise, 'Sheet Feuil1 is empty so .Find errors out.

I added an "On Error' statement





For i = LBound(SheetsFound) To UBound(SheetsFound)
Set Wks = Sheets(SheetsFound(i))
With Wks
'Sheet Feuil1 is empty so .Find errors out
On Error GoTo NextSheet ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
.Range("C2:C" & LR).Copy
ListsLR = Sheets("Lists").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Lists").Range("A" & ListsLR).PasteSpecial
.Range("D2:D" & LR).Copy
ListsLR = Sheets("Lists").Range("B" & Rows.Count).End(xlUp).Row + 1
Sheets("Lists").Range("B" & ListsLR).PasteSpecial
.Range("E2:E" & LR).Copy
ListsLR = Sheets("Lists").Range("C" & Rows.Count).End(xlUp).Row + 1
Sheets("Lists").Range("C" & ListsLR).PasteSpecial
.Range("F2:F" & LR).Copy
ListsLR = Sheets("Lists").Range("D" & Rows.Count).End(xlUp).Row + 1
Sheets("Lists").Range("D" & ListsLR).PasteSpecial
Application.CutCopyMode = False
End With

NextSheet:
On Error GoTo 0
Next i

djemy1975
11-12-2018, 12:41 AM
There were some data link errors in the attachment that I skipped


Based on what I could test otherwise, 'Sheet Feuil1 is empty so .Find errors out
.

I added an "On Error' statement





For i = LBound(SheetsFound) To UBound(SheetsFound)
Set Wks = Sheets(SheetsFound(i))
With Wks
'Sheet Feuil1 is empty so .Find errors out
On Error GoTo NextSheet ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
.Range("C2:C" & LR).Copy
ListsLR = Sheets("Lists").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Lists").Range("A" & ListsLR).PasteSpecial
.Range("D2:D" & LR).Copy
ListsLR = Sheets("Lists").Range("B" & Rows.Count).End(xlUp).Row + 1
Sheets("Lists").Range("B" & ListsLR).PasteSpecial
.Range("E2:E" & LR).Copy
ListsLR = Sheets("Lists").Range("C" & Rows.Count).End(xlUp).Row + 1
Sheets("Lists").Range("C" & ListsLR).PasteSpecial
.Range("F2:F" & LR).Copy
ListsLR = Sheets("Lists").Range("D" & Rows.Count).End(xlUp).Row + 1
Sheets("Lists").Range("D" & ListsLR).PasteSpecial
Application.CutCopyMode = False
End With

NextSheet:
On Error GoTo 0
Next i



First of all,thank you for your habitual interest in my posts .The code is working now but I want combobox6 (combobox to filter sheets)to show only three sheets ("BASIC";"SPECIFIC" and "IMPROVE")and I am unable to filter with year ("Année") and amounts ("Appro." "Appropr.16...etc) How can I get this fulfilled.
2318923190

What I want to do is to filter using only the three sheets mentioned above because when I add a sheet it is immedialtely affects the filter sheet "Lists".
Thanks in advance,

djemy1975
11-13-2018, 01:16 AM
I will mark this thread solved and and start again with the last 2 questions a new thread