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 ?