XL 2003 Question

[pullmyefinger]

ActiveCell.Formula = "=IF(AND('A'&EmptyRow>0,'B'&EmptyRow>0),'A'&EmptyRow-'A'&NewRow,0)"

This should be ez but it is giving me problems. App or Object-Defined error in a VB macro.

Just trying to create a formula to calculate the number of days for an interest payment. I can't hard code the payment date or the cell that contains the last date of payment. I figured

I could use ActiveCell to put a formula in the correct cell once I have all the other necessary info in other cells.

The formula needs to be =IF(AND(A9>0, B9>0), A9-A6,0) where

- EmptyRow is a variable that contains the next empty row where the new payment is to go.
- (Row) 9.
- NewRow is a variable that contains the row value of the last payment. (row) 6.
- The 6 is pasted as a value in cell O6 prior.
- The contents of column A are both date values, so I need A9-A6 to give me a value such as 29.

- I have to be doing something stupid cuz this is not a big deal.


This is what is in the macro now that is giving me the error:

ActiveCell.Formula = "=IF(AND('A'&EmptyRow>0,'B'&EmptyRow>0),'A'&EmptyRow-'A'&NewRow,0)"

What the heck am I doing wrong?

 

[Gary's Student]

Allow the variables to be calculated:

Sub qwerty()
   EmptyRow = 9
   NewRow = 6
   ActiveCell.Formula = "=IF(AND(A" & EmptyRow & ">0,B" & EmptyRow & ">0),A" & EmptyRow & "-A" & NewRow & ",0)"
End Sub

 

[pullmyefinger]

Holy Crap Batman!, that makes perfect sense. Thank You!!