Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this macro please
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this macro please
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 -- If this post helps click Yes --------------- Jacob Skaria "Alberta Rose" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy and paste values (columns)I have a macro file built | Excel Programming | |||
AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |