Help with Expenses Workbook parsing date and code info.

[Alvarado]

Spreadsheet in question:
http://www.filehosting.org/file/details/241730/Expense_Workbook.xlsx

I've been tasked with building a new expense spreadsheet for my boss and I've hit a bit of a rough patch. Basically our expense system is broken down by codes, of which there are 225. My boss would like to be able to enter in the Code, the Date, and the Amount on the "Expense Tracking" sheet (ie. Date: 5/25/11 Code: 6001-0001 Amount: $50.00) and have that total in the appropriate month and appropriate code on the Variance sheet.


I can't figure out how to write a function that will parse both the date and the code, without doing a giant IF, And function with all 225 codes for every field. I'm not extremely familiar with these type of functions, and I would really appreciate some help with figuring this out. If I need to upload the sheet a different way I can do that too, Thanks a lot for your help.

 

[bertie]

Hi and welcome to the forum.

I have tested the my code for the highlighted values:

Expense Tracking

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:85px;"><col style="width:79px;"><col style="width:84px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-weight:bold; ">Date</td><td style="font-weight:bold; ">Code</td><td style="font-weight:bold; ">Amount</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:right; ">5/15/11</td><td>6005-0000</td><td style="text-align:right; ">420</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="background-color:#ffff00; text-align:right; ">1/15/11</td><td style="background-color:#ffff00; ">6002-0000</td><td style="background-color:#ffff00; text-align:right; ">100</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="background-color:#ffff00; text-align:right; ">2/15/11</td><td style="background-color:#ffff00; ">6002-0000</td><td style="background-color:#ffff00; text-align:right; ">100</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="background-color:#ffff00; text-align:right; ">2/15/11</td><td style="background-color:#ffff00; ">6002-0000</td><td style="background-color:#ffff00; text-align:right; ">1000</td></tr></tbody></table>


And got these results:
Variance

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:106px;"><col style="width:253px;"><col style="width:84px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="background-color:#969696; color:#ffffff; font-weight:bold; ">6001-0000</td><td style="background-color:#969696; color:#ffffff; font-weight:bold; ">General & Administrative</td><td style="background-color:#969696; color:#ffffff; font-weight:bold; ">
</td><td style="background-color:#969696; color:#ffffff; font-weight:bold; ">
</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="background-color:#ffff00; ">6002-0000</td><td>Accounting & Legal</td><td style="background-color:#ccccff; text-align:right; ">100</td><td style="text-align:right; ">1100</td></tr></tbody></table>


I have made the assumption that the Expense Tracking sheet is filled out in this order Date - Code - Amount. When the amount is entered this triggers a worksheet change event.

Press Alt+F11
Double click the Expense Tracking module in the Project Window on the left hand side.
Copy and paste the code below.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
  [COLOR=darkblue]Dim[/COLOR] col [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] Code [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] Amount [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
  
  [COLOR=darkblue]If[/COLOR] Target.Column <> 3 [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
  
  col = Month(Target.Offset(, -2).Value) + 2
  Code = Target.Offset(, -1).Value
  Amount = Target.Value
  
  Module1.UpdateVariance col, Code, Amount
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

The change event assigns values to variables which are the passed to the UpdateVariance procedure in Module1.

In the VBA Editor click Insert => Module.
Ensure the name of the module is Module1.
Copy and paste the code below:

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] UpdateVariance([COLOR=darkblue]ByVal[/COLOR] col [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], _
                          [COLOR=darkblue]ByVal[/COLOR] Code [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], _
                          [COLOR=darkblue]ByVal[/COLOR] Amount [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR])
    [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Sheets("Variance")
      [COLOR=green]'find the row for the code[/COLOR]
      [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
      [COLOR=darkblue]Set[/COLOR] rng = .Columns(1).Find(What:=Code, _
                            After:=.Cells(2, 1), _
                            LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False, _
                            SearchFormat:=False)
      [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
      [COLOR=darkblue]If[/COLOR] rng [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        MsgBox "Code wasn't found!"
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=green]'update the amount for that month[/COLOR]
    rw = rng.Row
    [COLOR=darkblue]With[/COLOR] Sheets("Variance")
      Amount = Amount + [COLOR=darkblue]CDbl[/COLOR](.Cells(rw, col).Value)
      .Cells(rw, col).Value = Amount
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] rng = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

The code uses a Find routine to locate the code in the Variance sheet. From this we get the row the code is on.
So we now have the row and column, and we can use the cells() function to extract, update and replace the amount.

 

[Alvarado]

Wow that you very much for you help. That vba module is way more efficient than what i was going to attempt, guess i need to learn a bit!

Am i able to perform functions within the destination cells? Ultimately I need to show the difference between amounts that will be filled into the 'budget' sheet and the corresponding cells on the 'Variance' sheet, the output you helped me with. I know that I can do this easily by utilizing another sheet, i'm just new to Vba in excel and am curious how that output behaves.

 

[bertie]

The simplest way to do this would be to use the approach you suggested, i.e., set up another sheet to monitor the difference between budget and variance.

If we were to use VBA we would need to duplicate the UpdateVariance procedure to find the corresponding cell in the Budget sheet. And then perform some process on it. If the process changed you would need to amend the VBA code.

As you are new to VBA I would go with your new spreadsheet approach.