Bob, Thank you so much! I got it working. It's wonderful!

I had changed the location of the constant and missed changing it in a
location of the code.

I can't get over how great this works! Do you mind if I ask you some
questions? I would like to understand how you did this. Here's what I think
you did.

You disabled the auto update, right?

You set the Target to the entire worksheet?

After obtaining the last row you specified which cells you wanted to work
with and what you wanted done.

Oh, I think I get it. By disabling the auto update you forced it to wait
for the change, right?

A question though, the application enable events have been disabled. Where
are they enabled again? I see where they are enabled if there is an error.
Yet they are enabled again as the worksheet does update when I make a change.
Where does that happen?

Thanks,
Karen




"Bob Phillips" wrote:

That routine is a tad inefficient but should work assuming that there is
data in that range.

We are at a bit of an impasse, as it works okay in my tests, but not for
real for you.

Any chance that you could send me the workbook to check over?

--
---
HTH

Bob

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



"Karen53" wrote in message
...
Hi Dave,

Thank you for helping me with this.

It's still not working for me.

Here is my routine to determine my end row.


Private Sub EndRange(LastRow)

Dim iRow As Long
Dim TopRow As Long
Dim BottomRow As Long

iRow = 0
TopRow = 36
BottomRow = 337

With ActiveSheet

For iRow = BottomRow To TopRow Step -1
If Trim(.Cells(iRow, "E").Text) = "" Then
'keep looking
Else
LastRow = iRow
Exit For
End If

Next iRow

End With

End Sub


"Bob Phillips" wrote:

Karen,

You have a little routine that calculates the lastrow number. Is that
working correctly, and passing back a row number 36?

A small modification to make it a bit more efficient

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B33"
Dim iCtr As Long
Dim sNo As String
Dim sYes As String
Dim LastRow As Long

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

LastRow = 0

Call EndRange(LastRow)

If Range("B33").Value = "Yes" Then
For iCtr = 36 To LastRow
sYes = "=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)))))"
Me.Range("L" & iCtr).FormulaR1C1 = sYes
Next
ElseIf Range("B33").Value = "No" Then
For iCtr = 36 To LastRow
sNo = "=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)))))"
Me.Range("L" & iCtr).FormulaR1C1 = sNo
Next
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

--
---
HTH

Bob

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



"Karen53" wrote in message
...
Thanks Bob. I pasted this into the worksheet's module but the formula
does
not change when I make a selection. Have I missed something?


Thanks.


"Bob Phillips" wrote:

Okay, give this a whirl

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B33"
Dim iCtr As Long
Dim sNo As String
Dim sYes As String
Dim LastRow As Long

On Error GoTo ws_exit
Application.EnableEvents = False

LastRow = 0

Call EndRange(LastRow)

If Range("B33").Value = "Yes" Then
For iCtr = 36 To LastRow
sYes = "=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 = sYes
Next
ElseIf Range("B33").Value = "No" Then
For iCtr = 36 To LastRow
sNo = "=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 = sNo
Next
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
---
HTH

Bob

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



"Karen53" wrote in message
...
Yes, the yes/no DV is in B33 only. rows 36 down is where the
formulas
go.
No
DV there

Thanks


"Bob Phillips" wrote:

You mean that you want this macro to run when you make a selection
in
a
Data
Validation with just Yes/No in cell B33,?

Or is it DV in B36 down?
--
---
HTH

Bob

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