Hi Bob,

Sorry, I just noticed I called you Dave. You didn't know you were my
co-worker, did you?



"Karen53" wrote:

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)