Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/Coding issue
I receive a monthly spreadsheet (per project number) with approximately 30
columns. The columns are always in the same location, but there may be more or less rows each month depending on the project. I have inserted a column that needs some help coding. For example, Column F is where the cost types are located. Column U is the column that I've inserted that requires the coding. Depending on the cost type in column F, determines the formula that is used in column U. Example: Cost type 5830 (which appears in multiple rows), requires me to use the formula =max(G70,H70) (70 being the row number that would change with each row). Another example is cost type 5320 (which again appears in multiple rows), which would need the formula =if(M70<L70,N70*J70, max(G70,H70,N70*J70)). Is there VBA coding that would loop through each of the cells in the default U column, look into column F and determine which formula to use? And then continue to the next cell in column U and do the same thing? I am fairly new at Excel coding. Help please ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/Coding issue
The answer is yes, but in order to determine a best approach, it would be
helpful if you tell us how many cost types you have and, since you are new to VB, it would be helpful if you could list the cost types and associated formulas, if not too great in number (otherwise you will have to figure out how to write the code to insert the formula into the cells yourself). -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... I receive a monthly spreadsheet (per project number) with approximately 30 columns. The columns are always in the same location, but there may be more or less rows each month depending on the project. I have inserted a column that needs some help coding. For example, Column F is where the cost types are located. Column U is the column that I've inserted that requires the coding. Depending on the cost type in column F, determines the formula that is used in column U. Example: Cost type 5830 (which appears in multiple rows), requires me to use the formula =max(G70,H70) (70 being the row number that would change with each row). Another example is cost type 5320 (which again appears in multiple rows), which would need the formula =if(M70<L70,N70*J70, max(G70,H70,N70*J70)). Is there VBA coding that would loop through each of the cells in the default U column, look into column F and determine which formula to use? And then continue to the next cell in column U and do the same thing? I am fairly new at Excel coding. Help please ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/Coding issue
Sub Makeformula()
LastRow = Range("F" & Rows.Count).End(xlUp).Row For RowCount = 1 To LastRow Code = Range("F" & RowCount) Select Case Code Case 5320 MyFormula = "=if(M" & RowCount & "<L" & RowCount & _ ",N" & RowCount & "*J" & RowCount & ")" Case 5830 MyFormula = "=Max(G" & RowCount & ",H" & RowCount & ")" End Select Range("U" & RowCount).Formula = MyFormula Next RowCount End Sub "Alberta Rose" wrote: I receive a monthly spreadsheet (per project number) with approximately 30 columns. The columns are always in the same location, but there may be more or less rows each month depending on the project. I have inserted a column that needs some help coding. For example, Column F is where the cost types are located. Column U is the column that I've inserted that requires the coding. Depending on the cost type in column F, determines the formula that is used in column U. Example: Cost type 5830 (which appears in multiple rows), requires me to use the formula =max(G70,H70) (70 being the row number that would change with each row). Another example is cost type 5320 (which again appears in multiple rows), which would need the formula =if(M70<L70,N70*J70, max(G70,H70,N70*J70)). Is there VBA coding that would loop through each of the cells in the default U column, look into column F and determine which formula to use? And then continue to the next cell in column U and do the same thing? I am fairly new at Excel coding. Help please ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/Coding issue
I lost a piece of the formula. Use this instead
Sub Makeformula() LastRow = Range("F" & Rows.Count).End(xlUp).Row For RowCount = 1 To LastRow Code = Range("F" & RowCount) Select Case Code Case 5320 MyFormula = "=if(M" & RowCount & "<L" & RowCount & _ ",N" & RowCount & "*J" & RowCount & _ "max(G" & RowCount & ",H" & RowCount & _ ",N" & RowCount & "*J" & RowCount & "))" Case 5830 MyFormula = "=Max(G" & RowCount & ",H" & RowCount & ")" End Select Range("U" & RowCount).Formula = MyFormula Next RowCount End Sub "Alberta Rose" wrote: I receive a monthly spreadsheet (per project number) with approximately 30 columns. The columns are always in the same location, but there may be more or less rows each month depending on the project. I have inserted a column that needs some help coding. For example, Column F is where the cost types are located. Column U is the column that I've inserted that requires the coding. Depending on the cost type in column F, determines the formula that is used in column U. Example: Cost type 5830 (which appears in multiple rows), requires me to use the formula =max(G70,H70) (70 being the row number that would change with each row). Another example is cost type 5320 (which again appears in multiple rows), which would need the formula =if(M70<L70,N70*J70, max(G70,H70,N70*J70)). Is there VBA coding that would loop through each of the cells in the default U column, look into column F and determine which formula to use? And then continue to the next cell in column U and do the same thing? I am fairly new at Excel coding. Help please ? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/Coding issue
Cost types and formulas:
cost type 5110 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5117 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5119 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5130 uses =MAX(J99,M99) cost type 5310 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5317 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5319 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5320 uses various formulas depending on the cost code cost type 5327 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5329 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5330 uses =MAX(J99,M99) cost type 5511 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5521 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5531 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5610 uses =MAX(J99,M99) cost type 5620 uses =MAX(J99,M99) cost type 5690 uses =MAX(J99,M99) cost type 5830 uses =MAX(J99,M99) cost type 5910 uses =MAX(J99,M99) cost type 5970 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5980 uses =MAX(J99,M99) cost type 5950 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5941 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5943 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) Thanks for your help with this ....Laurie "Rick Rothstein" wrote: The answer is yes, but in order to determine a best approach, it would be helpful if you tell us how many cost types you have and, since you are new to VB, it would be helpful if you could list the cost types and associated formulas, if not too great in number (otherwise you will have to figure out how to write the code to insert the formula into the cells yourself). -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... I receive a monthly spreadsheet (per project number) with approximately 30 columns. The columns are always in the same location, but there may be more or less rows each month depending on the project. I have inserted a column that needs some help coding. For example, Column F is where the cost types are located. Column U is the column that I've inserted that requires the coding. Depending on the cost type in column F, determines the formula that is used in column U. Example: Cost type 5830 (which appears in multiple rows), requires me to use the formula =max(G70,H70) (70 being the row number that would change with each row). Another example is cost type 5320 (which again appears in multiple rows), which would need the formula =if(M70<L70,N70*J70, max(G70,H70,N70*J70)). Is there VBA coding that would loop through each of the cells in the default U column, look into column F and determine which formula to use? And then continue to the next cell in column U and do the same thing? I am fairly new at Excel coding. Help please ? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/Coding issue
Okay, with the exception of cost type 5320, which appears to need to look at
a different column, you only have 3 different formulas. Depending on the details of that exception, my recommendation might be to use more efficient worksheet formulas instead of VB code. Can you describe the details for cost type 5320 for us (we will need them no matter which way our recommendation go)? -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... Cost types and formulas: cost type 5110 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5117 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5119 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5130 uses =MAX(J99,M99) cost type 5310 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5317 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5319 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5320 uses various formulas depending on the cost code cost type 5327 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5329 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5330 uses =MAX(J99,M99) cost type 5511 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5521 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5531 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5610 uses =MAX(J99,M99) cost type 5620 uses =MAX(J99,M99) cost type 5690 uses =MAX(J99,M99) cost type 5830 uses =MAX(J99,M99) cost type 5910 uses =MAX(J99,M99) cost type 5970 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5980 uses =MAX(J99,M99) cost type 5950 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5941 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5943 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) Thanks for your help with this ....Laurie "Rick Rothstein" wrote: The answer is yes, but in order to determine a best approach, it would be helpful if you tell us how many cost types you have and, since you are new to VB, it would be helpful if you could list the cost types and associated formulas, if not too great in number (otherwise you will have to figure out how to write the code to insert the formula into the cells yourself). -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... I receive a monthly spreadsheet (per project number) with approximately 30 columns. The columns are always in the same location, but there may be more or less rows each month depending on the project. I have inserted a column that needs some help coding. For example, Column F is where the cost types are located. Column U is the column that I've inserted that requires the coding. Depending on the cost type in column F, determines the formula that is used in column U. Example: Cost type 5830 (which appears in multiple rows), requires me to use the formula =max(G70,H70) (70 being the row number that would change with each row). Another example is cost type 5320 (which again appears in multiple rows), which would need the formula =if(M70<L70,N70*J70, max(G70,H70,N70*J70)). Is there VBA coding that would loop through each of the cells in the default U column, look into column F and determine which formula to use? And then continue to the next cell in column U and do the same thing? I am fairly new at Excel coding. Help please ? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/Coding issue
How about something like this
Sub Makeformula() LastRow = Range("F" & Rows.Count).End(xlUp).Row For RowCount = 1 To LastRow Code = Range("F" & RowCount) Select Case Code Case 5110, 5117, 5119, 5310, 5317, 5319, 5320, 5327, 5329, _ 5511, 5521, 5531, 5970, 5950 Myformula = "=if(U" & RowCount & "<Q" & RowCount & _ ",U" & RowCount & "*AD" & RowCount & _ "max(J" & RowCount & ",M" & RowCount & _ ",U" & RowCount & "*AD" & RowCount & "))" Case 5130, 5330, 5610, 5620, 5690, 5830, 5910, 5980 Myformula = "=Max(J" & RowCount & ",M" & RowCount & ")" Case 5941, 5943, 5950 Myformula = "=IF(AND(J" & RowCount & "=0,M" & RowCount & "=0)," & _ "0,IF(J" & RowCount & "<(V" & RowCount & "*0.1)," & _ "MAX(J" & RowCount & ",M" & RowCount & ",J" & RowCount & "*Z90)," & _ "IF(V" & RowCount & "<R" & RowCount & ",V" & RowCount & "*AD" & RowCount & _ "," & "MAX(J" & RowCount & ",M" & RowCount & ",V" & RowCount & "*AD" & _ RowCount & ",J" & RowCount & "*Z" & RowCount & ")))) " End Select Range("U" & RowCount).Formula = Myformula Next RowCount End Sub "Rick Rothstein" wrote: Okay, with the exception of cost type 5320, which appears to need to look at a different column, you only have 3 different formulas. Depending on the details of that exception, my recommendation might be to use more efficient worksheet formulas instead of VB code. Can you describe the details for cost type 5320 for us (we will need them no matter which way our recommendation go)? -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... Cost types and formulas: cost type 5110 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5117 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5119 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5130 uses =MAX(J99,M99) cost type 5310 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5317 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5319 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5320 uses various formulas depending on the cost code cost type 5327 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5329 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5330 uses =MAX(J99,M99) cost type 5511 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5521 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5531 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5610 uses =MAX(J99,M99) cost type 5620 uses =MAX(J99,M99) cost type 5690 uses =MAX(J99,M99) cost type 5830 uses =MAX(J99,M99) cost type 5910 uses =MAX(J99,M99) cost type 5970 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5980 uses =MAX(J99,M99) cost type 5950 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5941 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5943 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) Thanks for your help with this ....Laurie "Rick Rothstein" wrote: The answer is yes, but in order to determine a best approach, it would be helpful if you tell us how many cost types you have and, since you are new to VB, it would be helpful if you could list the cost types and associated formulas, if not too great in number (otherwise you will have to figure out how to write the code to insert the formula into the cells yourself). -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... I receive a monthly spreadsheet (per project number) with approximately 30 columns. The columns are always in the same location, but there may be more or less rows each month depending on the project. I have inserted a column that needs some help coding. For example, Column F is where the cost types are located. Column U is the column that I've inserted that requires the coding. Depending on the cost type in column F, determines the formula that is used in column U. Example: Cost type 5830 (which appears in multiple rows), requires me to use the formula =max(G70,H70) (70 being the row number that would change with each row). Another example is cost type 5320 (which again appears in multiple rows), which would need the formula =if(M70<L70,N70*J70, max(G70,H70,N70*J70)). Is there VBA coding that would loop through each of the cells in the default U column, look into column F and determine which formula to use? And then continue to the next cell in column U and do the same thing? I am fairly new at Excel coding. Help please ? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/Coding issue
each cost type has a cost code as well. for all except the 5320, the
formulas needed are the same for any cost code. Cost code/cost type: 13210.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 20110.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 61103.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 61202.5320 uses =MAX(J99,M99) 61301.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 64201.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 69130.5320 uses =MAX(J99,M99) I appreciate your help with this :) "Rick Rothstein" wrote: Okay, with the exception of cost type 5320, which appears to need to look at a different column, you only have 3 different formulas. Depending on the details of that exception, my recommendation might be to use more efficient worksheet formulas instead of VB code. Can you describe the details for cost type 5320 for us (we will need them no matter which way our recommendation go)? -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... Cost types and formulas: cost type 5110 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5117 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5119 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5130 uses =MAX(J99,M99) cost type 5310 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5317 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5319 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5320 uses various formulas depending on the cost code cost type 5327 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5329 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5330 uses =MAX(J99,M99) cost type 5511 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5521 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5531 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5610 uses =MAX(J99,M99) cost type 5620 uses =MAX(J99,M99) cost type 5690 uses =MAX(J99,M99) cost type 5830 uses =MAX(J99,M99) cost type 5910 uses =MAX(J99,M99) cost type 5970 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5980 uses =MAX(J99,M99) cost type 5950 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5941 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5943 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) Thanks for your help with this ....Laurie "Rick Rothstein" wrote: The answer is yes, but in order to determine a best approach, it would be helpful if you tell us how many cost types you have and, since you are new to VB, it would be helpful if you could list the cost types and associated formulas, if not too great in number (otherwise you will have to figure out how to write the code to insert the formula into the cells yourself). -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... I receive a monthly spreadsheet (per project number) with approximately 30 columns. The columns are always in the same location, but there may be more or less rows each month depending on the project. I have inserted a column that needs some help coding. For example, Column F is where the cost types are located. Column U is the column that I've inserted that requires the coding. Depending on the cost type in column F, determines the formula that is used in column U. Example: Cost type 5830 (which appears in multiple rows), requires me to use the formula =max(G70,H70) (70 being the row number that would change with each row). Another example is cost type 5320 (which again appears in multiple rows), which would need the formula =if(M70<L70,N70*J70, max(G70,H70,N70*J70)). Is there VBA coding that would loop through each of the cells in the default U column, look into column F and determine which formula to use? And then continue to the next cell in column U and do the same thing? I am fairly new at Excel coding. Help please ? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/Coding issue
When I put this in and ran the macro, I just ran it for the 5830 cost type.
The macro went through column F and found the first instance of the 5830 appearing and put the formula in column U. It was a zero calculation, but it didn't stop there. It continued for each line after that even though it was not a 5830 cost type, until it came to the next cells in column F which had values in them, then it again copied the exact same formula. For example, the first instance of 5830 showing up was in cell F34. In cells U34 to U38, the exact same formula was copied. =max(G34,H34). In cell F39 was the next instance of 5830 showing up, the formula changed to =max(G39,H39). This exact formula was copied to the end of the worksheet in column U, even though there were no more instances of 5830 showing up. Am I missing something? Thanks...Laurie "joel" wrote: Sub Makeformula() LastRow = Range("F" & Rows.Count).End(xlUp).Row For RowCount = 1 To LastRow Code = Range("F" & RowCount) Select Case Code Case 5320 MyFormula = "=if(M" & RowCount & "<L" & RowCount & _ ",N" & RowCount & "*J" & RowCount & ")" Case 5830 MyFormula = "=Max(G" & RowCount & ",H" & RowCount & ")" End Select Range("U" & RowCount).Formula = MyFormula Next RowCount End Sub "Alberta Rose" wrote: I receive a monthly spreadsheet (per project number) with approximately 30 columns. The columns are always in the same location, but there may be more or less rows each month depending on the project. I have inserted a column that needs some help coding. For example, Column F is where the cost types are located. Column U is the column that I've inserted that requires the coding. Depending on the cost type in column F, determines the formula that is used in column U. Example: Cost type 5830 (which appears in multiple rows), requires me to use the formula =max(G70,H70) (70 being the row number that would change with each row). Another example is cost type 5320 (which again appears in multiple rows), which would need the formula =if(M70<L70,N70*J70, max(G70,H70,N70*J70)). Is there VBA coding that would loop through each of the cells in the default U column, look into column F and determine which formula to use? And then continue to the next cell in column U and do the same thing? I am fairly new at Excel coding. Help please ? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/Coding issue
I added an if to one of the cases
Sub Makeformula() LastRow = Range("F" & Rows.Count).End(xlUp).Row For RowCount = 1 To LastRow Cost = Range("F" & RowCount) CostArray = Split(CodeType, ".") CostCode = CostArray(0) CostType = CostArray(1) Select Case CostType Case 5110, 5117, 5119, 5310, 5317, 5319, 5320, 5327, 5329, _ 5511, 5521, 5531, 5970, 5950 If CostCode = 6102 Then Myformula = "=Max(J" & RowCount & ",M" & RowCount & ")" Else Myformula = "=if(U" & RowCount & "<Q" & RowCount & _ ",U" & RowCount & "*AD" & RowCount & _ "max(J" & RowCount & ",M" & RowCount & _ ",U" & RowCount & "*AD" & RowCount & "))" End If Case 5130, 5330, 5610, 5620, 5690, 5830, 5910, 5980 Myformula = "=Max(J" & RowCount & ",M" & RowCount & ")" Case 5941, 5943, 5950 Myformula = "=IF(AND(J" & RowCount & "=0,M" & RowCount & "=0)," & _ "0,IF(J" & RowCount & "<(V" & RowCount & "*0.1)," & _ "MAX(J" & RowCount & ",M" & RowCount & ",J" & RowCount & "*Z90)," & _ "IF(V" & RowCount & "<R" & RowCount & ",V" & RowCount & "*AD" & RowCount & _ "," & "MAX(J" & RowCount & ",M" & RowCount & ",V" & RowCount & "*AD" & _ RowCount & ",J" & RowCount & "*Z" & RowCount & ")))) " End Select Range("U" & RowCount).Formula = Myformula Next RowCount End Sub "Alberta Rose" wrote: each cost type has a cost code as well. for all except the 5320, the formulas needed are the same for any cost code. Cost code/cost type: 13210.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 20110.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 61103.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 61202.5320 uses =MAX(J99,M99) 61301.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 64201.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 69130.5320 uses =MAX(J99,M99) I appreciate your help with this :) "Rick Rothstein" wrote: Okay, with the exception of cost type 5320, which appears to need to look at a different column, you only have 3 different formulas. Depending on the details of that exception, my recommendation might be to use more efficient worksheet formulas instead of VB code. Can you describe the details for cost type 5320 for us (we will need them no matter which way our recommendation go)? -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... Cost types and formulas: cost type 5110 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5117 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5119 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5130 uses =MAX(J99,M99) cost type 5310 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5317 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5319 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5320 uses various formulas depending on the cost code cost type 5327 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5329 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5330 uses =MAX(J99,M99) cost type 5511 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5521 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5531 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5610 uses =MAX(J99,M99) cost type 5620 uses =MAX(J99,M99) cost type 5690 uses =MAX(J99,M99) cost type 5830 uses =MAX(J99,M99) cost type 5910 uses =MAX(J99,M99) cost type 5970 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5980 uses =MAX(J99,M99) cost type 5950 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5941 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5943 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) Thanks for your help with this ....Laurie "Rick Rothstein" wrote: The answer is yes, but in order to determine a best approach, it would be helpful if you tell us how many cost types you have and, since you are new to VB, it would be helpful if you could list the cost types and associated formulas, if not too great in number (otherwise you will have to figure out how to write the code to insert the formula into the cells yourself). -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... I receive a monthly spreadsheet (per project number) with approximately 30 columns. The columns are always in the same location, but there may be more or less rows each month depending on the project. I have inserted a column that needs some help coding. For example, Column F is where the cost types are located. Column U is the column that I've inserted that requires the coding. Depending on the cost type in column F, determines the formula that is used in column U. Example: Cost type 5830 (which appears in multiple rows), requires me to use the formula =max(G70,H70) (70 being the row number that would change with each row). Another example is cost type 5320 (which again appears in multiple rows), which would need the formula =if(M70<L70,N70*J70, max(G70,H70,N70*J70)). Is there VBA coding that would loop through each of the cells in the default U column, look into column F and determine which formula to use? And then continue to the next cell in column U and do the same thing? I am fairly new at Excel coding. Help please ? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/Coding issue
Assuming your data starts on Row 2, and that the Cost Code are in Column E,
put this formula in U2 and copy it down... =IF(ISNUMBER(MATCH(F2,{5110,5117,5119,5310,5317,53 19,5327,5329,5511,5521,5531,5970},0)),IF(U2<Q2,U2* AD2,MAX(J2,M2,U2*AD2)),IF(ISNUMBER(MATCH(F2,{5130, 5330,5610,5620,5690,5830,5910,5980},0)),MAX(J2,M2) ,IF(ISNUMBER(MATCH(F2,{5941,5943,5950},0)),IF(AND( J2=0,M2=0),0,IF(J2<(V2*0.1),MAX(J2,M2,J2*Z2),IF(V2 <R2,V2*AD2,MAX(J2,M2,V2*AD2,J2*Z2)))),IF(ISNUMBER( MATCH(E2,{61202,69130},0)),MAX(J2,M2),IF(ISNUMBER( MATCH(E2,{13210,20110,61103,61301,64201},0)),IF(U2 <Q2,U2*AD2,MAX(J2,M2,U2*AD2)),""))))) -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... each cost type has a cost code as well. for all except the 5320, the formulas needed are the same for any cost code. Cost code/cost type: 13210.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 20110.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 61103.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 61202.5320 uses =MAX(J99,M99) 61301.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 64201.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 69130.5320 uses =MAX(J99,M99) I appreciate your help with this :) "Rick Rothstein" wrote: Okay, with the exception of cost type 5320, which appears to need to look at a different column, you only have 3 different formulas. Depending on the details of that exception, my recommendation might be to use more efficient worksheet formulas instead of VB code. Can you describe the details for cost type 5320 for us (we will need them no matter which way our recommendation go)? -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... Cost types and formulas: cost type 5110 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5117 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5119 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5130 uses =MAX(J99,M99) cost type 5310 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5317 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5319 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5320 uses various formulas depending on the cost code cost type 5327 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5329 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5330 uses =MAX(J99,M99) cost type 5511 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5521 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5531 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5610 uses =MAX(J99,M99) cost type 5620 uses =MAX(J99,M99) cost type 5690 uses =MAX(J99,M99) cost type 5830 uses =MAX(J99,M99) cost type 5910 uses =MAX(J99,M99) cost type 5970 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5980 uses =MAX(J99,M99) cost type 5950 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5941 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5943 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) Thanks for your help with this ....Laurie "Rick Rothstein" wrote: The answer is yes, but in order to determine a best approach, it would be helpful if you tell us how many cost types you have and, since you are new to VB, it would be helpful if you could list the cost types and associated formulas, if not too great in number (otherwise you will have to figure out how to write the code to insert the formula into the cells yourself). -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... I receive a monthly spreadsheet (per project number) with approximately 30 columns. The columns are always in the same location, but there may be more or less rows each month depending on the project. I have inserted a column that needs some help coding. For example, Column F is where the cost types are located. Column U is the column that I've inserted that requires the coding. Depending on the cost type in column F, determines the formula that is used in column U. Example: Cost type 5830 (which appears in multiple rows), requires me to use the formula =max(G70,H70) (70 being the row number that would change with each row). Another example is cost type 5320 (which again appears in multiple rows), which would need the formula =if(M70<L70,N70*J70, max(G70,H70,N70*J70)). Is there VBA coding that would loop through each of the cells in the default U column, look into column F and determine which formula to use? And then continue to the next cell in column U and do the same thing? I am fairly new at Excel coding. Help please ? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/Coding issue
I get an error in the coding from Case 5941.... to End Select. It is in red.
And is CostCode 6102 supposed to be 61202? "joel" wrote: I added an if to one of the cases Sub Makeformula() LastRow = Range("F" & Rows.Count).End(xlUp).Row For RowCount = 1 To LastRow Cost = Range("F" & RowCount) CostArray = Split(CodeType, ".") CostCode = CostArray(0) CostType = CostArray(1) Select Case CostType Case 5110, 5117, 5119, 5310, 5317, 5319, 5320, 5327, 5329, _ 5511, 5521, 5531, 5970, 5950 If CostCode = 6102 Then Myformula = "=Max(J" & RowCount & ",M" & RowCount & ")" Else Myformula = "=if(U" & RowCount & "<Q" & RowCount & _ ",U" & RowCount & "*AD" & RowCount & _ "max(J" & RowCount & ",M" & RowCount & _ ",U" & RowCount & "*AD" & RowCount & "))" End If Case 5130, 5330, 5610, 5620, 5690, 5830, 5910, 5980 Myformula = "=Max(J" & RowCount & ",M" & RowCount & ")" Case 5941, 5943, 5950 Myformula = "=IF(AND(J" & RowCount & "=0,M" & RowCount & "=0)," & _ "0,IF(J" & RowCount & "<(V" & RowCount & "*0.1)," & _ "MAX(J" & RowCount & ",M" & RowCount & ",J" & RowCount & "*Z90)," & _ "IF(V" & RowCount & "<R" & RowCount & ",V" & RowCount & "*AD" & RowCount & _ "," & "MAX(J" & RowCount & ",M" & RowCount & ",V" & RowCount & "*AD" & _ RowCount & ",J" & RowCount & "*Z" & RowCount & ")))) " End Select Range("U" & RowCount).Formula = Myformula Next RowCount End Sub "Alberta Rose" wrote: each cost type has a cost code as well. for all except the 5320, the formulas needed are the same for any cost code. Cost code/cost type: 13210.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 20110.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 61103.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 61202.5320 uses =MAX(J99,M99) 61301.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 64201.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 69130.5320 uses =MAX(J99,M99) I appreciate your help with this :) "Rick Rothstein" wrote: Okay, with the exception of cost type 5320, which appears to need to look at a different column, you only have 3 different formulas. Depending on the details of that exception, my recommendation might be to use more efficient worksheet formulas instead of VB code. Can you describe the details for cost type 5320 for us (we will need them no matter which way our recommendation go)? -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... Cost types and formulas: cost type 5110 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5117 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5119 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5130 uses =MAX(J99,M99) cost type 5310 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5317 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5319 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5320 uses various formulas depending on the cost code cost type 5327 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5329 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5330 uses =MAX(J99,M99) cost type 5511 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5521 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5531 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5610 uses =MAX(J99,M99) cost type 5620 uses =MAX(J99,M99) cost type 5690 uses =MAX(J99,M99) cost type 5830 uses =MAX(J99,M99) cost type 5910 uses =MAX(J99,M99) cost type 5970 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5980 uses =MAX(J99,M99) cost type 5950 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5941 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5943 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) Thanks for your help with this ....Laurie "Rick Rothstein" wrote: The answer is yes, but in order to determine a best approach, it would be helpful if you tell us how many cost types you have and, since you are new to VB, it would be helpful if you could list the cost types and associated formulas, if not too great in number (otherwise you will have to figure out how to write the code to insert the formula into the cells yourself). -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... I receive a monthly spreadsheet (per project number) with approximately 30 columns. The columns are always in the same location, but there may be more or less rows each month depending on the project. I have inserted a column that needs some help coding. For example, Column F is where the cost types are located. Column U is the column that I've inserted that requires the coding. Depending on the cost type in column F, determines the formula that is used in column U. Example: Cost type 5830 (which appears in multiple rows), requires me to use the formula =max(G70,H70) (70 being the row number that would change with each row). Another example is cost type 5320 (which again appears in multiple rows), which would need the formula =if(M70<L70,N70*J70, max(G70,H70,N70*J70)). Is there VBA coding that would loop through each of the cells in the default U column, look into column F and determine which formula to use? And then continue to the next cell in column U and do the same thing? I am fairly new at Excel coding. Help please ? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/Coding issue
I fixed the last problem I messaged you about. There was a _ missing from
the end of the string. Now I get an error on CostCode=CostArray(0). What does this coding mean? "joel" wrote: I added an if to one of the cases Sub Makeformula() LastRow = Range("F" & Rows.Count).End(xlUp).Row For RowCount = 1 To LastRow Cost = Range("F" & RowCount) CostArray = Split(CodeType, ".") CostCode = CostArray(0) CostType = CostArray(1) Select Case CostType Case 5110, 5117, 5119, 5310, 5317, 5319, 5320, 5327, 5329, _ 5511, 5521, 5531, 5970, 5950 If CostCode = 6102 Then Myformula = "=Max(J" & RowCount & ",M" & RowCount & ")" Else Myformula = "=if(U" & RowCount & "<Q" & RowCount & _ ",U" & RowCount & "*AD" & RowCount & _ "max(J" & RowCount & ",M" & RowCount & _ ",U" & RowCount & "*AD" & RowCount & "))" End If Case 5130, 5330, 5610, 5620, 5690, 5830, 5910, 5980 Myformula = "=Max(J" & RowCount & ",M" & RowCount & ")" Case 5941, 5943, 5950 Myformula = "=IF(AND(J" & RowCount & "=0,M" & RowCount & "=0)," & _ "0,IF(J" & RowCount & "<(V" & RowCount & "*0.1)," & _ "MAX(J" & RowCount & ",M" & RowCount & ",J" & RowCount & "*Z90)," & _ "IF(V" & RowCount & "<R" & RowCount & ",V" & RowCount & "*AD" & RowCount & _ "," & "MAX(J" & RowCount & ",M" & RowCount & ",V" & RowCount & "*AD" & _ RowCount & ",J" & RowCount & "*Z" & RowCount & ")))) " End Select Range("U" & RowCount).Formula = Myformula Next RowCount End Sub "Alberta Rose" wrote: each cost type has a cost code as well. for all except the 5320, the formulas needed are the same for any cost code. Cost code/cost type: 13210.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 20110.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 61103.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 61202.5320 uses =MAX(J99,M99) 61301.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 64201.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 69130.5320 uses =MAX(J99,M99) I appreciate your help with this :) "Rick Rothstein" wrote: Okay, with the exception of cost type 5320, which appears to need to look at a different column, you only have 3 different formulas. Depending on the details of that exception, my recommendation might be to use more efficient worksheet formulas instead of VB code. Can you describe the details for cost type 5320 for us (we will need them no matter which way our recommendation go)? -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... Cost types and formulas: cost type 5110 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5117 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5119 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5130 uses =MAX(J99,M99) cost type 5310 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5317 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5319 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5320 uses various formulas depending on the cost code cost type 5327 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5329 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5330 uses =MAX(J99,M99) cost type 5511 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5521 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5531 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5610 uses =MAX(J99,M99) cost type 5620 uses =MAX(J99,M99) cost type 5690 uses =MAX(J99,M99) cost type 5830 uses =MAX(J99,M99) cost type 5910 uses =MAX(J99,M99) cost type 5970 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5980 uses =MAX(J99,M99) cost type 5950 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5941 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5943 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) Thanks for your help with this ....Laurie "Rick Rothstein" wrote: The answer is yes, but in order to determine a best approach, it would be helpful if you tell us how many cost types you have and, since you are new to VB, it would be helpful if you could list the cost types and associated formulas, if not too great in number (otherwise you will have to figure out how to write the code to insert the formula into the cells yourself). -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... I receive a monthly spreadsheet (per project number) with approximately 30 columns. The columns are always in the same location, but there may be more or less rows each month depending on the project. I have inserted a column that needs some help coding. For example, Column F is where the cost types are located. Column U is the column that I've inserted that requires the coding. Depending on the cost type in column F, determines the formula that is used in column U. Example: Cost type 5830 (which appears in multiple rows), requires me to use the formula =max(G70,H70) (70 being the row number that would change with each row). Another example is cost type 5320 (which again appears in multiple rows), which would need the formula =if(M70<L70,N70*J70, max(G70,H70,N70*J70)). Is there VBA coding that would loop through each of the cells in the default U column, look into column F and determine which formula to use? And then continue to the next cell in column U and do the same thing? I am fairly new at Excel coding. Help please ? |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA/Coding issue
I change a variable name to make the code easier to understand and forgot to
change it in one place. The error occured because there was no period in the string and didn't create and split didn't create an array from CostArray = Split(CodeType, ".") to CostArray = Split(Cost, ".") I found a minor error in on eof the formulas here is the correction Sub test() RowCount = 1 Cost = Range("F" & RowCount) CostArray = Split(Cost, ".") CostCode = CostArray(0) CostType = CostArray(1) End Sub Sub Makeformula() LastRow = Range("F" & Rows.Count).End(xlUp).Row For RowCount = 1 To LastRow Cost = Range("F" & RowCount) CostArray = Split(Cost, ".") CostCode = CostArray(0) CostType = CostArray(1) Select Case CostType Case 5110, 5117, 5119, 5310, 5317, 5319, 5320, 5327, 5329, _ 5511, 5521, 5531, 5970, 5950 If CostCode = 6102 Then Myformula = "=Max(J" & RowCount & ",M" & RowCount & ")" Else Myformula = "=if(U" & RowCount & "<Q" & RowCount & _ ",U" & RowCount & "*AD" & RowCount & _ ",max(J" & RowCount & ",M" & RowCount & _ ",U" & RowCount & "*AD" & RowCount & "))" End If Case 5130, 5330, 5610, 5620, 5690, 5830, 5910, 5980 Myformula = "=Max(J" & RowCount & ",M" & RowCount & ")" Case 5941, 5943, 5950 Myformula = "=IF(AND(J" & RowCount & "=0,M" & RowCount & "=0)," & _ "0,IF(J" & RowCount & "<(V" & RowCount & "*0.1)," & _ "MAX(J" & RowCount & ",M" & RowCount & ",J" & RowCount & "*Z90)," & _ "IF(V" & RowCount & "<R" & RowCount & ",V" & RowCount & "*AD" & _ RowCount & "," & "MAX(J" & RowCount & _ ",M" & RowCount & ",V" & RowCount & "*AD" & _ RowCount & ",J" & RowCount & "*Z" & RowCount & ")))) " End Select Range("U" & RowCount).Formula = Myformula Next RowCount End Sub "Alberta Rose" wrote: I fixed the last problem I messaged you about. There was a _ missing from the end of the string. Now I get an error on CostCode=CostArray(0). What does this coding mean? "joel" wrote: I added an if to one of the cases Sub Makeformula() LastRow = Range("F" & Rows.Count).End(xlUp).Row For RowCount = 1 To LastRow Cost = Range("F" & RowCount) CostArray = Split(CodeType, ".") CostCode = CostArray(0) CostType = CostArray(1) Select Case CostType Case 5110, 5117, 5119, 5310, 5317, 5319, 5320, 5327, 5329, _ 5511, 5521, 5531, 5970, 5950 If CostCode = 6102 Then Myformula = "=Max(J" & RowCount & ",M" & RowCount & ")" Else Myformula = "=if(U" & RowCount & "<Q" & RowCount & _ ",U" & RowCount & "*AD" & RowCount & _ "max(J" & RowCount & ",M" & RowCount & _ ",U" & RowCount & "*AD" & RowCount & "))" End If Case 5130, 5330, 5610, 5620, 5690, 5830, 5910, 5980 Myformula = "=Max(J" & RowCount & ",M" & RowCount & ")" Case 5941, 5943, 5950 Myformula = "=IF(AND(J" & RowCount & "=0,M" & RowCount & "=0)," & _ "0,IF(J" & RowCount & "<(V" & RowCount & "*0.1)," & _ "MAX(J" & RowCount & ",M" & RowCount & ",J" & RowCount & "*Z90)," & _ "IF(V" & RowCount & "<R" & RowCount & ",V" & RowCount & "*AD" & RowCount & _ "," & "MAX(J" & RowCount & ",M" & RowCount & ",V" & RowCount & "*AD" & _ RowCount & ",J" & RowCount & "*Z" & RowCount & ")))) " End Select Range("U" & RowCount).Formula = Myformula Next RowCount End Sub "Alberta Rose" wrote: each cost type has a cost code as well. for all except the 5320, the formulas needed are the same for any cost code. Cost code/cost type: 13210.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 20110.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 61103.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 61202.5320 uses =MAX(J99,M99) 61301.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 64201.5320 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) 69130.5320 uses =MAX(J99,M99) I appreciate your help with this :) "Rick Rothstein" wrote: Okay, with the exception of cost type 5320, which appears to need to look at a different column, you only have 3 different formulas. Depending on the details of that exception, my recommendation might be to use more efficient worksheet formulas instead of VB code. Can you describe the details for cost type 5320 for us (we will need them no matter which way our recommendation go)? -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... Cost types and formulas: cost type 5110 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5117 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5119 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5130 uses =MAX(J99,M99) cost type 5310 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5317 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5319 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5320 uses various formulas depending on the cost code cost type 5327 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5329 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5330 uses =MAX(J99,M99) cost type 5511 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5521 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5531 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5610 uses =MAX(J99,M99) cost type 5620 uses =MAX(J99,M99) cost type 5690 uses =MAX(J99,M99) cost type 5830 uses =MAX(J99,M99) cost type 5910 uses =MAX(J99,M99) cost type 5970 uses =IF(U99<Q99,U99*AD99,MAX(J99,M99,U99*AD99)) cost type 5980 uses =MAX(J99,M99) cost type 5950 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5941 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) cost type 5943 uses =IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M9 0,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD9 0,J90*Z90)))) Thanks for your help with this ....Laurie "Rick Rothstein" wrote: The answer is yes, but in order to determine a best approach, it would be helpful if you tell us how many cost types you have and, since you are new to VB, it would be helpful if you could list the cost types and associated formulas, if not too great in number (otherwise you will have to figure out how to write the code to insert the formula into the cells yourself). -- Rick (MVP - Excel) "Alberta Rose" wrote in message ... I receive a monthly spreadsheet (per project number) with approximately 30 columns. The columns are always in the same location, but there may be more or less rows each month depending on the project. I have inserted a column that needs some help coding. For example, Column F is where the cost types are located. Column U is the column that I've inserted that requires the coding. Depending on the cost type in column F, determines the formula that is used in column U. Example: Cost type 5830 (which appears in multiple rows), requires me to use the formula =max(G70,H70) (70 being the row number that would change with each row). Another example is cost type 5320 (which again appears in multiple rows), which would need the formula =if(M70<L70,N70*J70, max(G70,H70,N70*J70)). Is there VBA coding that would loop through each of the cells in the default U column, look into column F and determine which formula to use? And then continue to the next cell in column U and do the same thing? I am fairly new at Excel coding. Help please ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Coding issue with Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 coding issue | Excel Discussion (Misc queries) | |||
Please please help!! Weird coding issue | Excel Discussion (Misc queries) | |||
Excel VBA coding - Outlook and Shut down issue | Excel Programming | |||
Excel VBA coding - Outlook and Shut down issue | Excel Worksheet Functions |