User Input For Loop Issues

[Jietoh]

Hello All!

I have an unusual problem. I receive monthly reports that have a list of acct #'s and monthly spending. If an account does not spend anything in the month then they are omitted from the report (I cannot change this), this creates a headache when I go to add the new data to my yearly report since that report contains every account. So, I wrote a macro about a year ago that would compare the acct # column (sort column) in the monthly report against another acct # column (match column) that contains every acct # that I oversee. If the two didn't match, it would insert a blank cell and bump the acct # down and try again until a match was found then it would offset and try the next. This works wonderfully, and saves me a ton of time.

Then I had the bright idea of creating a generic version of this code that would use user input to determine the sort column, match column, and loop number. I am encountering a number of problems with this and will list them below. I also attached a file with my dummy data, and what I've come up with so far.

1: Code needs to prompt user for Sort Column, Match column, and Loop number
2: Code needs to properly sort list down adding blanks where needed.
3: If possible Sort Column should be expanded to include nearby cells that will be carried with it, but not considered when sorting.

Thanks. Book123.xlsm

[TMS]

Why do you insert blank cells? If it's just to line the entries up, why don't you just use VLOOKUP or INDEX/MATCH? I'm sure the process could be much simpler.

It would probably help people to help you if you upload a more realistic sample file with a typical master and transaction file.

Might still be appropriate to use macros, but I suspect there could be a different and more effective approach.


Regards, TMS

[LJMetzger]

Hi Jietoh,

If I understand the problem correctly, you want to import monthly data into a yearly report. If you know a few tricks, sorting the data is not needed. See the attached file which contains the code below. This should help you get started.

Assumptions and Pseudo Facts:
a. Sheet 'Yearly Report' contains simulated yearly data per your example.
b. Sheet 'Monthly Report' contains simulated monthly data per your example.
c. I assumed column 'B' (Data2) and Columm 'C' (Data4) were to be imported from the Monthly Report into the Yearly Report.
d. Account Numbers were in Column 'A' of both Sheets.

How the Software Works:
a. The Yearly Report Columns for 'Data2' and 'Data4' are cleared.
b. Created a list (Scripting Dictionary) of all the Account Numbers in the Monthly Sheet. This will fail after approximately 10,000 to 20,000 different Account Numbers. Each Account Number is stored with it's associated Row Number on the Monthly Report.
NOTE: Account Numbers must be UNIQUE on the Monthly Report
c. Each row of the Yearly Report is processed.
d. If the Account Number for a Yearly Report row is in the Dictionary, 'Data2' and 'Data4' are read from the Monthly Report and put into the Yearly Report.

In ordinary code module such as ModMonthlyIntoYearly or Module1:

Please Login or Register  to view this content.

Lewis

[Jietoh]

Lewis,

Thanks for this. I need to really dig into this.

Dan