Receiving Various Run Time Errors On A Simple Piece of Code

**HarryGreenwood** · 12-04-2015, 06:29 AM

Morning folks,

Could someone please review the attached sheet and elaborate on why i cant get it to work!

All it needs to do is produce a list of unique values for the SUMIFS formula to populate costs on.

Trying to use it as an alternative to a pivot table.

pivottable alternative for autoloader.xlsm

Cheers,

HG

**gmr4evr1** · 12-04-2015, 11:13 AM

HG,
Part of the issue was that you didn't declare variables, like sRow, fRow etc. Also, use Option Explicit at the beginning of your code then "step" through your code and it will usually highlight the problem line(s) of code for you. It wont tell you specifically what's wrong with it, but at least you will know where the error is. to step through code, click any line in your code then press F8, this will go line by line through you code. Here is updated code for you...it may still error out on the ActiveSheet.Range...... line, but I'm not sure why yet..

Please Login or Register  to view this content.

**HarryGreenwood** · 12-04-2015, 11:15 AM

Yeah I understand all of the option explicit etc stuff, i was stepping through when i was getting an error on the Active.Sheet Range, which like you, i cant understand!

VBA is great, but the errors are consistently a mystery to me.

**gmr4evr1** · 12-04-2015, 11:17 AM

LOL, they are to me as well....although some of them I understand a little better than I used to.

**HarryGreenwood** · 12-04-2015, 11:17 AM

The dreaded 1001 is the worst one, just an unknown, none categorized error.

I've had to re-write code around that error about 200 times!

**gmr4evr1** · 12-04-2015, 11:27 AM

Luckily, I haven't seen that one to often....I don't think.
I think I have narrowed it down to the Action part of the code. When I break it down and step through it, it highlights that word.

**6StringJazzer** · 12-04-2015, 12:34 PM

Here is your runtime error BUT it won't solve your overall problem--you still have some issues. Remove the code in red.

Please Login or Register  to view this content.

The destination for your advanced filter copy overlaps with the source data. You are copying from column B:D and pasting to D. Also you are including the cost column in your copy, so nearly every row is unique anyway. Although I was able to do this manually in Excel with no error, it gave weird results and I have to wonder why you want to do that. Based on your formulas, I think it is an error.

Let's take a step back and talk about what it is you want to do. You want a unique list to use SUMIF to calculate costs. Please look at the attached and see if that gets you there.

To be clear, you see the result but I updated your macro and formulas so you should be able to wipe out columns F:G and run the macro again.