Good morning all,
I could use some advice on implementation of VBA for work. I've been lurking this forum and others trying to figure this out but have had pretty much zero luck.
The background:
I need to track cash register variance, and have been tasked with modernizing our system. I have approximately 70 registers across six locations that need I am responsible for, and if this works, it may go company wide. we use this to identify accounting and operational errors, as well as to catch employees who are stealing.
The data:
I receive a weekly excel file showing registers that are out of balance. The report shows date, register number, location number, and variance by tender type (register expected $100 in cash, bank processed $90, report shows positive $10 in cash. Don't ask why it's reversed like this... if there is an overage it is reported as a negative. Easy fix though).
The desired outcome:
I have a master spreadsheet that I currently input these weekly results into by hand. It allows for easy visualization to research patterns, specific incidents, etc. I input the numbers onto the master chart, then color code based on type/cause and insert comments as needed.
The spreadsheet is set up with dates in the columns and register numbers in the rows.
I have successfully used SumIfs and Index Match Match inside the individual cells to pull data from the weekly report (I append another sheet in the workbook with the raw report).
The problem is that when I edit the individual cells (delete variance because accounting fixed an operational issue etc), the formula will be deleted as well. Plus the file size is a concern, as I have to email this out from time to time. Lastly, I have encountered similar workbooks trying to do this that have locked my computer for long periods with "calculating" taunting me from the bottom right of my screen.
The desired outcome:
Create a macro that will do the following:
Using either an Access DB or the other worksheet I've been appending,
Match register and date and input value
IF
over or under 1/-1
AND
Automatically color code based on tender type (yellow cash, orange check, grey coupon)
I need it to ignore if there is a value already to cut down on processing time (and ignore if there is a comment, if possible)
I've been trying to create this code, but I feel like I'm trying to write graduate level Chinese without even knowing the alphabet. I've spent hours trying to learn VBA without going ANYWHERE.
Output area details:
Worksheet is "Chart"
First row is dates (with breaks at the month end for totals).
Named "date"
Second column is register numbers
Named "reg"
Intersection of date/reg is where I want the data dropped
Named "var"
Source area details:
Worksheet is "variance_log"
Individual variances reported in rows.
Columns are (in order):
Date/location description/register number/total/cash/check/coupon/a bunch of tenders that don't matter/location number.
Named date column "Date1"
Named register column "Reg1"
Named tender columns "Total" "Cash" "Check" "Coupon"
Before I go any deeper into this rabbit hole, I could use some advice.
I was thinking SumIfs in VBA would be an ok implementation, but I'm worried about the calculation time. The variance_log sheet is approximately 6000 rows already, and it's only May. Would an Index Match Match function in VBA be more efficient?
Currently I have only snippets of code that is all red in the VBE on my work computer (posting this from home...I need a life).
So any input, help...anything would be appreciated greatly, even if it's just general ideas on implementation or suggestions for structural changes.
I could use some advice on implementation of VBA for work. I've been lurking this forum and others trying to figure this out but have had pretty much zero luck.
The background:
I need to track cash register variance, and have been tasked with modernizing our system. I have approximately 70 registers across six locations that need I am responsible for, and if this works, it may go company wide. we use this to identify accounting and operational errors, as well as to catch employees who are stealing.
The data:
I receive a weekly excel file showing registers that are out of balance. The report shows date, register number, location number, and variance by tender type (register expected $100 in cash, bank processed $90, report shows positive $10 in cash. Don't ask why it's reversed like this... if there is an overage it is reported as a negative. Easy fix though).
The desired outcome:
I have a master spreadsheet that I currently input these weekly results into by hand. It allows for easy visualization to research patterns, specific incidents, etc. I input the numbers onto the master chart, then color code based on type/cause and insert comments as needed.
The spreadsheet is set up with dates in the columns and register numbers in the rows.
I have successfully used SumIfs and Index Match Match inside the individual cells to pull data from the weekly report (I append another sheet in the workbook with the raw report).
The problem is that when I edit the individual cells (delete variance because accounting fixed an operational issue etc), the formula will be deleted as well. Plus the file size is a concern, as I have to email this out from time to time. Lastly, I have encountered similar workbooks trying to do this that have locked my computer for long periods with "calculating" taunting me from the bottom right of my screen.
The desired outcome:
Create a macro that will do the following:
Using either an Access DB or the other worksheet I've been appending,
Match register and date and input value
IF
over or under 1/-1
AND
Automatically color code based on tender type (yellow cash, orange check, grey coupon)
I need it to ignore if there is a value already to cut down on processing time (and ignore if there is a comment, if possible)
I've been trying to create this code, but I feel like I'm trying to write graduate level Chinese without even knowing the alphabet. I've spent hours trying to learn VBA without going ANYWHERE.
Output area details:
Worksheet is "Chart"
First row is dates (with breaks at the month end for totals).
Named "date"
Second column is register numbers
Named "reg"
Intersection of date/reg is where I want the data dropped
Named "var"
Source area details:
Worksheet is "variance_log"
Individual variances reported in rows.
Columns are (in order):
Date/location description/register number/total/cash/check/coupon/a bunch of tenders that don't matter/location number.
Named date column "Date1"
Named register column "Reg1"
Named tender columns "Total" "Cash" "Check" "Coupon"
Before I go any deeper into this rabbit hole, I could use some advice.
I was thinking SumIfs in VBA would be an ok implementation, but I'm worried about the calculation time. The variance_log sheet is approximately 6000 rows already, and it's only May. Would an Index Match Match function in VBA be more efficient?
Currently I have only snippets of code that is all red in the VBE on my work computer (posting this from home...I need a life).
So any input, help...anything would be appreciated greatly, even if it's just general ideas on implementation or suggestions for structural changes.
