Repeating formulas in VBA

[Guest]

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

 

[Bob Phillips]

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

--

HTH

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

 

[Guest]

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?

 

[Tom Ogilvy]

just loop over your ranges and and change the appropriate inputs, then
evaluate your formula.

 

[Guest]

Hello Mr. Ogilvy. I hadn't seen your around in a bit. I had hoped you
hadn't gone away.

I apologize but I don't understand exactly what you mean below. I have
confidence that if you say it, it will work. I just don't know how to loop
over the ranges???

 

[Tom Ogilvy]

Dim MRRPYTo As Range
for each MRRPYTo in WSRep.Range("B7").Resize(10,1)
MRRPYTo.Offset(0,3).Resize(1,8).Name = "MRRRange"
'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
Next

I am guessing at where your values are located, but this represents looping
from B7 to B16 (10 cells in a column) and for each of those cells, setting
MRRrange to columns E to L in that row specific row (rows 7 to 16, row by
row), then doing the evaluation and returning the results to the cell in
column B of that row (MRRPYTo). Then moving on to the next row to repeat
the evaluation.

That is just an example and you would have to figure out how to address the
specific cells you want to reference.

 

[Guest]

I don't think I am being clear on what I am asking. Notice the code below:

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

If UnVa.Value = "Provider" Then
BufYes.Value = WSDSD.Evaluate("=SUMPRODUCT((LabRrange=" &
ProVa & ")*(LabRange<>0)*(Yearrange=" & PaYrVa & ")*(Typerange=""" &
TyVa.Value & """)*(Locrange=""" & LocVa.Value & """))")
BufNo.Value = WSDSD.Evaluate("=SUMPRODUCT((LabRrange=" &
ProNoVa & ")*(LabRange<>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


Notice, the VBA is repeated and the only variable that changed was MMRRange
to LabRange. I will use the same formula in the VBA back to back just
changing this one variable over and over. Is there an easier way than
repeating all that code. Perhaps your last answer answers this and I am just
not skilled enough to know it?

 

[Tom Ogilvy]

Dim v as Variant, v1 as Variant
Dim s1 as String, s2 as String, s3 as String
Dim s4 as String
v = Array("MRRrange", "LabRange")
v1 = Array("B7","B8")

s1 = "=SUMPRODUCT(("
s2 = "=" & ProVa & ")*("
s3 = "<>0)*(Yearrange=" & PaYrVa & ")*(Typerange=""" & _
TyVa.Value & """)*(Locrange=""" & LocVa.Value & """))"

s4 = "=" & ProNoVa & ")*("

If UnVa.Value = "Provider" Then
for i = lbound(v) to ubound(v)
set MRRPYTo to Range(v1(i))

BufYes.Value = WSDSD.Evaluate(s1 & v(i) & s2 & v(i) & s3)
BufNo.Value = WSDSD.Evaluate(s1 & v(i) & s4 & v(i) & s3)
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
Next
End If


Would be one approach. Add your ranges in the same pattern as presented.