Bob,

I had to add another column to update the formula. I understood your
instructions and believe I did it correctly. At least I did not get an error
messeage, but now it is not working. My thought is that perhaps there is
something I need to add because of the Indirect or vloopup statements?

Thanks for your help!

If Range("B26").Value = "Yes" Then
For iCtr = 36 To LastRow
'update the Pro-Rata Share formula
CAMYes = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr &
"C10" & _
"=""No"",0,IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _
"C16,IF(R6C2=0,(R" & iCtr & "C11* R" & iCtr & _
"C9),(R" & iCtr & "C11* R" & iCtr &
"C9)/365*(R6C2)))))"
Me.Range("L" & iCtr).FormulaR1C1 = CAMYes

'update the Pro-Rata Share Percentage
PercentYes = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" &
iCtr & "C10" & "=""No""," & _
"0, Indirect(Vlookup('Line Items'!!$R" & iCtr - 21 &
"C3,CAMPerCentLoc,3,False)))"
Me.Range("K" & iCtr).FormulaR1C1 = PercentYes
Next
ElseIf Range("B26").Value = "No" Then
For iCtr = 36 To LastRow
'update the Pro-Rata Share formula
CAMNo = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr &
"C10" & "=""No""," & _
"IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _
"C16,IF(R6C2=0,(R" & iCtr & "C11* R" & iCtr & _
"C7),(R" & iCtr & "C11* R" & iCtr & "C7)/365*(R6C2))))"
Me.Range("L" & iCtr).FormulaR1C1 = CAMNo

'update the Pro-Rata Share Percentage
PercentNo = "=IF(ISBLANK(R" & iCtr & "C10),""""," & _
"Indirect(Vlookup('Line Items'!!$R" & iCtr - 21 &
"C3,CAMPerCentLoc,3,False)))"
Me.Range("K" & iCtr).FormulaR1C1 = PercentNo
Next
End If
End If





"Bob Phillips" wrote:

Karen,

In short,

the whole expression has to be enclosed in a single double-quote character
(") at the start and at the end

if you want to evaluate a variable within that expression, you have to close
the string (single double-quote), concatenate the variable ( & varname &),
and then re-open the string (single double-quote)

quotes within the sting itself have to be doubled up, so as to tell the
compiler that it is not the end of the string, but an included quote

if you use a continuation character, you must close the string, add a
concatenation character before the continuation character (" & _), and then
on the next like re-open the string again (single double-quote)

In summary

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

becomes

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

by adding " & before the _ on the first line, and " before the If on the
second line.

--
---
HTH

Bob

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



"Karen53" wrote in message
...
Bob,

I had to revise one of my statements and now I get an error. Would you
mind
explaining how you determine where to put the quotes on these? I think I
understand but obviously I don't. Since I'm working with a lot of them I
don't want to have to keep asking for help.

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

I error out at the If(Isnumber). The quotes around the quotes take care
of
themselves, don't they? So the opening quote beginning with the first
line
"C10 should still be good and close with the (isnumber(R" shouldn't it?

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