My VBA calculation on sheet "$$$" in column L gives a "Runtime error 13: Type mismatch" when I run it, using the "original calculate balance" button. This is confusing because this has worked for years without problem, until I made a change to the formula on sheet "Template" in column C to leave rows w/out data "blank" with
Spreadsheet here: XLS File
What I've determined is this: Blank cells created w/ the
"" and copied to another sheet, are causing the runtime error when I perform a simple addition calculation with VBA. Excel is treating those "blank" cells as text and it is breaking the addition formula.
The steps to repeat the error follow:
1) The formula on sheet "Template", col C, results in "blank" cells w/ the
"", as it is supposed to. This is automatic. The formula is:
2) On sheet "$$$", press the "Insert Template" button. This copies the range "Template_RecurringExp" from sheet "Template" and pastes it into sheet "$$$" in columns I-K after the last used row. I leave the blank rows in the template for ease in entering manual data in between the templated rows.
3) Press "Original Calculate Balance" on sheet "$$$". This will calculate the balance from the values in the rows above and values in column K. The VBA (found in module "SumBalanceOriginal") for this is:
Option Explicit Sub RunningBalO() 'creates a running balance column 'assumes starting bal is the first cell of bal col 'assumes +/- transactions are 1 column to the left of the bal col & 1 row below starting bal Application.ScreenUpdating = False Dim NumRows As Integer Dim colDebit As Integer Dim tempSum As Single Dim i As Integer Dim ws As Worksheet Dim cStartBal As Range Dim RowStart As Integer Dim colBal As Integer Dim coldebitltr As String Set ws = ThisWorkbook.Sheets("$$$") Set cStartBal = ws.Range("l4") 'L4 tempSum = cStartBal.Value 'starting balance for the PENDING transactions colDebit = cStartBal.Column - 1 colBal = cStartBal.Column RowStart = cStartBal.Row + 1 'First row with debits, used to set "i" in loop 'Find last used row in debit column coldebitltr = Split(Cells(1, colDebit).Address, "$")(1) 'Converts the column # to a letter to pass to func LastRow 'Debug.Print "col letter for debits= " & coldebitltr NumRows = LastRow(coldebitltr, ws) + 1 'call func LastRow for "K" and Suntrust 'Debug.Print "NumRows= " & NumRows 'Exit Sub 'Debug.Print "col# for Debit= " & colDebit 'Debug.Print "Rowstart= " & RowStart 'Calculate & fill running balance With ws For i = RowStart To NumRows '"i = " starting row of debits tempSum = tempSum - ws.Cells(i, colDebit).Value ' Debug.Print "sum = " & tempSum 'Debug.Print "i = " & i ws.Cells(i, colBal).Value = Format(tempSum, "0.00") Next i End With Application.ScreenUpdating = True End Sub
The runtime error occurs on the
tempSum = tempSum - ws.Cells(i, colDebit).Value line.
All the courses of action I can think of are:
1) Find a way to get rid of the "blank" cell and turn it into an empty cell w/out the
"". I've not yet found anything online to do this. There was something about using
NA() instead of
"", but it didn't look like it would work.
2) Convert the "blank" cells in column K into empty cells when pasting via the "InsertTemplate" macro executed by the "Insert Template" button. Not sure how to do that.
3) Edit the VBA formula to ignore the "blank" text cells.
I added this if statement before the calculation in module "SumBalanceRevised":
If IsNumeric(ws.Cells(i, colDebit).Value) Then
tempSum = tempSum - ws.Cells(i, colDebit).Value
It is executed by the button called "Revised Calculate Balance". That results in the calculation skipping over the empty rows. It looks like the calculations are correct. I would prefer that it didn't leave empty cells in the Balance column and fill completely down the column, like in column M - the testing column. It is not apparent to me how to make that happen.
4) Add VBA to the "Calculate Balance" VBA to convert the "blank" cells in col K to either be empty cells or not text.
Any suggestions on how to make either of the COAs work or an alternative not listed is appreciated.