VBA/Coding issue

A

Alberta Rose

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 ?
 
R

Rick Rothstein

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).
 
J

joel

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
 
J

joel

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
 
A

Alberta Rose

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,M90,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD90,J90*Z90))))
cost type 5941 uses
=IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M90,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD90,J90*Z90))))
cost type 5943 uses
=IF(AND(J90=0,M90=0),0,IF(J90<(V90*0.1),MAX(J90,M90,J90*Z90),IF(V90<R90,V90*AD90,MAX(J90,M90,V90*AD90,J90*Z90))))

Thanks for your help with this ....Laurie
 
R

Rick Rothstein

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)?
 
J

joel

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
 
A

Alberta Rose

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 :)
 
A

Alberta Rose

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
 
J

joel

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
 
R

Rick Rothstein

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,5319,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)),"")))))
 
A

Alberta Rose

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?
 
A

Alberta Rose

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?
 
J

joel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with this macro please 1
Excel/VBA Coding Help 4
Continue formula until reaching a blank cell. 5
Sales Schedule 3
Coding Question 3
Find coding 5
VBA Coding Question 3
Autoadjust range declaration coding when inserting rows 4

Top