Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating formulas in VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating formulas in VBA
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating formulas in VBA
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating formulas in VBA
just loop over your ranges and and change the appropriate inputs, then
evaluate your formula. -- Regards, Tom Ogilvy "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating formulas in VBA
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" wrote: just loop over your ranges and and change the appropriate inputs, then evaluate your formula. -- Regards, Tom Ogilvy "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating formulas in VBA
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. -- Regards, Tom Ogilvy "scrabtree23" wrote in message ... 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" wrote: just loop over your ranges and and change the appropriate inputs, then evaluate your formula. -- Regards, Tom Ogilvy "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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating formulas in VBA
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" wrote: 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. -- Regards, Tom Ogilvy "scrabtree23" wrote in message ... 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" wrote: just loop over your ranges and and change the appropriate inputs, then evaluate your formula. -- Regards, Tom Ogilvy "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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating formulas in VBA
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. -- Regards, Tom Ogilvy "scrabtree23" wrote in message ... 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" wrote: 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. -- Regards, Tom Ogilvy "scrabtree23" wrote in message ... 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" wrote: just loop over your ranges and and change the appropriate inputs, then evaluate your formula. -- Regards, Tom Ogilvy "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to repeating many formulas | Excel Discussion (Misc queries) | |||
Repeating formulas.... | Excel Discussion (Misc queries) | |||
formulas are not repeating across the board. | Excel Worksheet Functions | |||
Repeating Formulas | Excel Worksheet Functions | |||
repeating formulas | Excel Discussion (Misc queries) |