Help with this macro please

A

Alberta Rose

I have a spreadsheet that I need help with. Depending on the cost types, I
want Excel to go look at the column with the cost types in and return the
appropriate formula to the default column and then to to the next cell and do
the same, repeat to the end.

Below is a list of the cost types and corresponding formulas.

=IF(AND(K" & RowCount & "=0,N " & RowCount & "=0),0,IF(M " & RowCount & "<(R
" & RowCount & "*0.1),MAX(K " & RowCount & ", N " & RowCount & ", K " &
RowCount & "*AD " & RowCount & "), IF(V " & RowCount & "<U " & RowCount & ",
AA " & RowCount & " *AH" & RowCount & ",MAX(K " & RowCount & ",N " & RowCount
& ",AA " & RowCount & "*AH " & RowCount & "))))

for these Cost Types:
5515
5931
5932
5933
5934
5941
5943
5950
=IF(V " & RowCount & "<U " & RowCount & ",AA " & RowCount & " * AH " &
RowCount & ", MAX(K " & RowCount & ", N " & RowCount & ", AA " & RowCount &
"*AH " & RowCount & "))
Cost Types:5110
5119
5310
5317
5319
5320
5329
5511
5531

=MAX(K " & RowCount & ", N " & RowCount & ")
Cost Types:5117
5130
5327
5330
5521
5610
5620
5690
5830
5910
5980


Thanks for any help you can give me.

Laurie
 
J

Jacob Skaria

Dear Rose

Assuming you have headers in row1, Cost type in Col A..the below macro will
assign the formulas in ColB....I havent tested this, but you should be able
to modify to suit your requirements.....Test and feedback..

Sub Macro1()

Dim RowCount As Long
Dim lngLastRow As Long
Dim CostType As Integer

'Get last row with data in Column A
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

'Loop until the last row
For RowCount = 2 To lngLastRow
'Get costype from Col A and convert into integer
CostType = CInt("0" & Range("A" & RowCount))

Select Case CostType
Case 5515, 5931, 5932, 5933, 5934, 5941, 5943, 5950
Range("B" & RowCount) = "=IF(AND(K" & RowCount & "=0,N" & _
RowCount & "=0),0,IF(M" & RowCount & "<(R" & RowCount & _
"*0.1),MAX(K" & RowCount & ",N" & RowCount & ",K" & RowCount & _
"*AD" & RowCount & "), IF(V" & RowCount & "<U" & RowCount _
& ",AA" & RowCount & "*AH" & RowCount & ",MAX(K" & RowCount & _
",N" & RowCount & ",AA" & RowCount & "*AH" & RowCount & "))))"

Case 5110, 5119, 5310, 5317, 5319, 5320, 5329, 5511, 5531
Range("B" & RowCount) = "=IF(V" & RowCount & "<U" & RowCount & _
",AA" & RowCount & "*AH" & RowCount & ",MAX(K" & RowCount & _
",N" & RowCount & ",AA" & RowCount & "*AH" & RowCount & "))"


Case 5117, 5130, 5327, 5330, 5521, 5610, 5620, 5690, _
5830, 5910, 5980
Range("B" & RowCount) = "=MAX(K" & RowCount & ",N" & RowCount & ")"
End Select
Next

End Sub
 
Top