Macro Help

[Standinghawk]

I am having a problem with a line in a Macro. When the Macro is run, it is supposed to transfer the line items in a checkbook register and sort them into a report by type. the problem is that if there are no lines of a particular type, the subtotal cell reads Err:522. I know this is because there are no lines to count and transfer, but I don't know enough about Macro Coding to fix it. I have fixed a lot of things in this code, but this has me stumped.

Here is the file;

download/file.php?id=29607

I suspect the problem is in this line (close to the bottom of the checkbook Main Macro);

_SummaryCellLast.Formula = "=" & _StartingBalanceCell & "+" & _TotalCreditCell & "-" & _TotalDebitCell

What I want to do is use this formula, which fixes the problem in Calc;

=IF(_SummaryCellLast.Formula <>"";_SummaryCellLast.Formula = "=" & _StartingBalanceCell & "+" & _TotalCreditCell & "-" & _TotalDebitCell;"")

I don't know if this formula will work the same way in a Macro as it does in the cell, but basicly, I want the subtotal to be blank if there are no line totals to total. Any help would be greatly appreciated. Thank you all.

[FJCC]

If I understand you correctly, you need to use code something like

Code: Select all

IF _SummaryCellLast.Formula <>"" Then 
		_SummaryCellLast.Formula = "=" & _StartingBalanceCell & "+" & _TotalCreditCell & "-" & _TotalDebitCell
	Else 
	 _SummaryCellLast.Formula = ""
	End IF

The ELSE is redundant as the code is written because it is only triggered if _SummaryCellLast.Formula is already "".

[Standinghawk]

Thanks FJCC, But I still get the error. Did you plug it into the file I uploaded?

[FJCC]

I ran the code by pressing the transfer button and got no error.

[Standinghawk]

FJCC, did you run it with just, say, one or two line items? It's when I don't have any line items of one or more Category line items that the error appears in the report. Try it with just the first line, the Buy-in dated 1.12.16. You will see the errors pop up in the Balance column (AA). It doesn't show up if there is at least 1 line for each of the Categories (ie Buy-in, Payout, Income, Expense). I did actually try the code you suggested, and I still got the same error, so I thought maybe the code doesn't work the same way, or maybe I needed to use quotes ("") on some of it that I wasn't aware of. Also, I noticed that the subtotal at the bottom of the Balance column (AA) is not showing, which tells me that the formula is not working the way we think it should.

Thanks for the help.

[FJCC]

This seems to avoid the errors. In the blocks of code where you define the location and formula of _SummaryCell1, _SummaryCell2, _SummaryCell3, and _SummaryCell4, put an If Then Else around the line where you set the formula. If there are no entries for the corresponding category, the variables like _counterPayout in the example below will have a value of zero. When that happens, do not set the Formula just set the value to zero.

Code: Select all

	_SummaryCell2_Row = _TargetRowStart + _counterBuyin + 2 + _counterPayout
	_SummaryCell2 = _Sheet.getCellByPosition(_TargetCellRange.getRangeAddress.EndColumn, _SummaryCell2_Row)
	if _counterPayout <> 0 Then
		_SummaryCell2.Formula = "=SUM(" & _TargetCol & _TargetRowStart + 1 + 2 + _counterBuyin & ":" & _TargetCol & _SummaryCell2_Row & ")"	
		'STANDINGHAWK Set Your desired font size for payouts summary
		_SummaryCell2.CharHeight = 10
	Else
		_SummaryCell2.Value = 0
	End If

[rajew]

Hello Standinghawk,

Haven't been checking here awhile, sorry for missing Your initial question. The solution from FJCC is correct, just remeber to put it on all blocks of SummaryCells from 1 to 4, using the appropriate counter in the IF statement (_counterPayin for 1, _counterPayout for 2 and so on).
Also, You don't see Your last subtotal formula, because of this:

Code: Select all

IF _SummaryCellLast.Formula <>"" Then
      _SummaryCellLast.Formula = "=" & _StartingBalanceCell & "+" & _TotalCreditCell & "-" & _TotalDebitCell
   Else
    _SummaryCellLast.Formula = ""
   End IF

Once You have that error protection for each conter this IF is unncessary (the initial condition is never true), unless You plan on introducing string values to subtotals that have 0 value (like for ex. "No debits this month" in the debit subtotal instead of simply 0). If so, You would need to build the last formula "dynamically" as in the attached file.