I don't think that is exactly what I need??? I don't need the values in the
range to change. I need the formula to change so it referecnes a different
range. It is a very long formula that will have to be repeated and only one
little part of the formula will be changing. How can I avoid re-writing the
long formula everytime when only one little part of it is changing?

"Bob Phillips" wrote:

A. Look up Autofill in help.
B. It skips it

--

HTH

RP
(remove nothere from the email address if mailing direct)


"scrabtree23" wrote in message
...
Below is a sample of my code. You can see that all the "programing" is
designed to get the correct answer in cell "B7". I will need this formula
to
repeat like 50 times with the only variables changing being MRRPYTo and
the
MRRrange. How can I avoid all the lines of code and simply this? A
second
question, if you use "IF Then End If" arguments in your VBA Code, when the
code is read to the If and the criteria are not met, does the reading skip
then until the line after the "End If" and pick up again there, or does it
still read through the code between the If and End If?

Dim MRRPYTo As Range
Set MRRPYTo = WSRep.Range("B7")

'When the unit is Provider (County Specific)
If UnVa.Value = "Provider" Then
BufYes.Value = WSDSD.Evaluate("=SUMPRODUCT((MRRrange=" &
ProVa & ")*(MRRrange<0)*(Yearrange=" & PaYrVa & ")*(Typerange=""" &
TyVa.Value & """)*(Locrange=""" & LocVa.Value & """))")
BufNo.Value = WSDSD.Evaluate("=SUMPRODUCT((MRRrange=" &
ProNoVa & ")*(MRRrange<0)*(Yearrange=" & PaYrVa & ")*(Typerange=""" &
TyVa.Value & """)*(Locrange=""" & LocVa.Value & """))")
BufTot.Value = BufYes.Value + BufNo.Value
MRRPYTo.Value = 0
If BufYes < 0 And BufTot < 0 Then
MRRPYTo.Value = BufYes.Value / (BufYes.Value +
BufNo.Value)
End If
End If