Issue with debugging Advanced filter Macro

Question

I have been racking my brain with trying to debug my Advanced Filter macro on Excel. I'm creating a spreadsheet for Inventory/ Budget tracking

Sheet3.Range("A2:J" & LastProjItemRow).AdvancedFilter xlFilterCopy, CriteriaRange:=Sheet3.Range("L2:L3"), CopyToRange:=Sheet3.Range("N2:W2"), Unique:=True

Can anyone help with the debugging please?

It keeps giving me the run-time error '1004'

The extract range has a missing or invalid field name

Answer 1

PPatte,

In proc Project_Load() this line:

Range("F3:F7,I4:I5,D10:H999,J10:K999").ClearContents 'Clear Fields

misses the period at the start and so should be:

.Range("F3:F7,I4:I5,D10:H999,J10:K999").ClearContents 'Clear Fields

otherwise it clears the contents on the active sheet and that of course is not always Sheet1.

In Sheet3 (Project items) the header of column N (in N2) is not the same as the one in A2 and L2.

It misses (again) a period.

That was the reason that the advanced filter didn’t work.

Hope this helps.

Jan

Answer 2

PPatte,

It seems to me that you didn't include the header-row of the list you are filtering so perhaps this formula will do:

Sheet3.Range("A1:J" & LastProjItemRow).AdvancedFilter xlFilterCopy, CriteriaRange:=Sheet3.Range("L2:L3"), CopyToRange:=Sheet3.Range("N1:W1"), Unique:=True

(filtering on one value in L3)

I assume that Range("L2:L3") has the header in L2 but if it is in L1 then the formula should be:

Sheet3.Range("A1:J" & LastProjItemRow).AdvancedFilter xlFilterCopy, CriteriaRange:=Sheet3.Range("L1:L3"), CopyToRange:=Sheet3.Range("N1:W1"), Unique:=True

(filtering on two values in L2 and L3)

Jan

I attempted those 2 different code and the same issue keeps popping up. Seeing more of the code would be helpful.

LastProjItemRow = Sheet3.Range("A99999").End(xlUp).Row 'Last Item Row

If LastProjItemRow < 3 Then GoTo NoItems

Sheet3.Range("A2:J" & LastProjItemRow).AdvancedFilter xlFilterCopy, CriteriaRange:=Sheet3.Range("L2:L3"), CopyToRange:=Sheet3.Range("N2:W2"), Unique:=True

LastItemResultRow = Sheet3.Range("N99999").End(xlUp).Row

If LastProjItemRow < 3 Then GoTo NoItems

For ResultRow = 3 To LastItemResultRow

ProjItemRow = Sheet3.Range("V" & ResultRow).Value 'Project Item Row

.Range("D" & ProjItemRow & ":H" & ProjItemRow).Value = Sheet3.Range("O" & ResultRow & ":S" & ResultRow).Value 'Item Details

.Range("J" & ProjItemRow & ":K" & ProjItemRow).Value = Sheet3.Range("T" & ResultRow & ":U" & ResultRow).Value 'Actual Costs

.Range("Q" & ProjItemRow).Value = Sheet3.Range("W" & ResultRow).Value 'DB Row

Next ResultRow

Answer 3

PPatte,

It seems to me that you didn't include the header-row of the list you are filtering so perhaps this formula will do:

Sheet3.Range("A1:J" & LastProjItemRow).AdvancedFilter xlFilterCopy, CriteriaRange:=Sheet3.Range("L2:L3"), CopyToRange:=Sheet3.Range("N1:W1"), Unique:=True

(filtering on one value in L3)

I assume that Range("L2:L3") has the header in L2 but if it is in L1 then the formula should be:

Sheet3.Range("A1:J" & LastProjItemRow).AdvancedFilter xlFilterCopy, CriteriaRange:=Sheet3.Range("L1:L3"), CopyToRange:=Sheet3.Range("N1:W1"), Unique:=True

(filtering on two values in L2 and L3)

Jan

Answer 4

Hi PPatte! Run-time error '1004' is a common error in Excel VBA that indicates an issue with the syntax or the range specified in the macro code. However, there are a few things you can check to debug your Advanced Filter macro: * Check the range references: Ensure that the range references in your macro are correct and match the range of cells you want to filter. The variable "LastProjItemRow" should be properly defined and its value should be greater than or equal to 2. * Check the criteria range: Ensure that the criteria range in your macro is properly defined and includes the correct criteria for filtering. In your case, the criteria range is specified as "Sheet3.Range("L2:L3")". Ensure that this range includes the correct criteria you want to filter by. * Check the destination range: Ensure that the destination range in your macro is properly defined and is large enough to hold the filtered results. In your case, the destination range is specified as "Sheet3.Range("N2:W2")". Ensure that this range is large enough to hold the filtered data. * Check for any typos or syntax errors: Review your macro code carefully for any typos or syntax errors. Ensure that all keywords and arguments are spelled correctly and all parentheses and commas are in the right place. * You can also try running the macro step-by-step using the VBA debugger to help identify the issue. To do this, open the VBA editor, set a breakpoint on the line of code that is giving you the error, and then run the macro in debug mode. This will allow you to step through the code line-by-line and see where the error is occurring. Hopefully, these tips will help you debug your Advanced Filter macro and get it working properly. Kindly let me know, if you require additional assistance, I will be glad to help further. Best Regards, Shakiru

I have attempted the steps listed and still am having issues. Would you need to see more of the code to help determine the issue?

Thank you for taking the time helping me Shakiru

Best Regards,

Patrick

Answer 5

Hi PPatte!

Run-time error '1004' is a common error in Excel VBA that indicates an issue with the syntax or the range specified in the macro code.

However, there are a few things you can check to debug your Advanced Filter macro:

* Check the range references: Ensure that the range references in your macro are correct and match the range of cells you want to filter. The variable "LastProjItemRow" should be properly defined and its value should be greater than or equal to 2.

* Check the criteria range: Ensure that the criteria range in your macro is properly defined and includes the correct criteria for filtering. In your case, the criteria range is specified as "Sheet3.Range("L2:L3")". Ensure that this range includes the correct criteria you want to filter by.

* Check the destination range: Ensure that the destination range in your macro is properly defined and is large enough to hold the filtered results. In your case, the destination range is specified as "Sheet3.Range("N2:W2")". Ensure that this range is large enough to hold the filtered data.

* Check for any typos or syntax errors: Review your macro code carefully for any typos or syntax errors. Ensure that all keywords and arguments are spelled correctly and all parentheses and commas are in the right place.

* You can also try running the macro step-by-step using the VBA debugger to help identify the issue. To do this, open the VBA editor, set a breakpoint on the line of code that is giving you the error, and then run the macro in debug mode. This will allow you to step through the code line-by-line and see where the error is occurring.

Hopefully, these tips will help you debug your Advanced Filter macro and get it working properly.

Kindly let me know, if you require additional assistance, I will be glad to help further.

Best Regards, Shakiru