Hi Bob,

Thank you! That is much better.

A question though. How do I get the macro to run once I make my selection?
As it is, it runs as soon as I click the cell to select my choice (It's a
validation list). I have to click out of the cell and back on it again for
it to run with the new selection.


Thanks



"Bob Phillips" wrote:

Karen,

I am not sure that I am understanding the issue/problem here, but a couple
of observations.

Your code has two NoFormula declarations, no YesFormula.

You have to re-define the formula each time to make sure it picks up the
current index value, otherwise it will be with an index of 0 for all
iterations.

I also don't like selecting, it causes problems.

Does this do any better?

Private Sub Contributions()
Dim iCtr As Long
Dim NoFormula As String
Dim YesFormula As String
Dim LastRow As Long

LastRow = 0

Call EndRange(LastRow)

If Range("B33").Value = "Yes" Then
For iCtr = 36 To LastRow
YesFormula = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr &
"C10" & _
"=""Yes"",0,IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr
& _
"C16,IF(ISBLANK(R6C2),(R" & iCtr & "C11* R" & iCtr &
_
"C7),(R" & iCtr & "C11* R" & iCtr &
"C7)/365*(R6C2)))))"
Range("L" & iCtr).FormulaR1C1 = YesFormula
Next
ElseIf Range("B33").Value = "No" Then
For iCtr = 36 To LastRow
NoFormula = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr &
"C10" & _
"=""No"",0,IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr &
_
"C16,IF(ISBLANK(R6C2),(R" & iCtr & "C11* R" & iCtr &
_
"C9),(R" & iCtr & "C11* R" & iCtr &
"C9)/365*(R6C2)))))"
Range("L" & iCtr).FormulaR1C1 = NoFormula
Next
End If

End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Karen53" wrote in message
...
Thank you Bob.

I now find it is not waiting for me to make a selection (it's a validation
list). I have others which do not have a for next loop which I have to
click
another target cell before it updates. This takes off like a race horse.
This is being called from a Worksheet_Change procedure which when I Step
into
I find it goes back up to when I don't expect it to, on the first for
next.
It inserted blank cells even though there is a value in column "J". Can
you
help me with this? I know the formula works as I had it on the sheet
initially. I need to insert it programmatically.

Thanks

Private Sub Contributions()

Dim iCtr As Long
Dim NoFormula As String
Dim YesFormula As String
Dim LastRow As Long

NoFormula = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & _
"=""No"",0,IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _
"C16,IF(ISBLANK(R6C2),(R" & iCtr & "C11* R" & iCtr & _
"C9),(R" & iCtr & "C11* R" & iCtr & "C9)/365*(R6C2)))))"

NoFormula = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & _
"=""No"",0,IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _
"C16,IF(ISBLANK(R6C2),(R" & iCtr & "C11* R" & iCtr & _
"C7),(R" & iCtr & "C11* R" & iCtr & "C7)/365*(R6C2)))))"

LastRow = 0

Call EndRange(LastRow)

If Range("B33").Value = "Yes" Then
For iCtr = 36 To LastRow
Range("L" & iCtr).Select
ActiveCell.FormulaR1C1 = YesFormula
Next
End If

If Range("B33").Value = "No" Then
For iCtr = 36 To LastRow
Range("L" & iCtr).Select
ActiveCell.FormulaR1C1 = NoFormula
Next
End If

End Sub




"Bob Phillips" wrote:

sFormula = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr & "C10" & _
"=""No"",0,IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _
"C16,IF(ISBLANK(R6C2),(R" & iCtr & "C11* R" & iCtr & _
"C9),(R" & iCtr & "C11* R" & iCtr & "C9)/365*(R6C2)))))"
ActiveCell.FormulaR1C1 = sFormula



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Karen53" wrote in message
...
Hi,

I've been going round and round with this. I don't see what is wrong.
The
formula works perfectly in the spreadsheet but it won't let me insert
it
programmatically.

original formula
=IF(ISBLANK($J36),"",IF($J36="No",0,IF(ISNUMBER($P 42),$P42,IF(ISBLANK($B$6),
_($K42*$G42),($K42*$G42)/365*($B$6)))))

Insert formula
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(R" & iCtr & "C10),"",""IF(R" &
iCtr
&
"C10) _=""No"",0,IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr &
"C16,IF(ISBLANK(R6C2),(R" _ & iCtr & "C11* R" & iCtr & "C9),(R" & iCtr
&
"C11)* R" & iCtr & "C9/365* _(R6C2)))))"

I get an Application defined or Object defined error.

Thanks.