Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Automate Row Results.
I wonder if you can help me to setup this macro.
I have values located in the excel cell as shown below: A B C D E F G H 1 bag 24 24 24 =SUM(A1:C1) 63 top 2 tube 24 24 21 =SUM(A2:C2) 3 cord 24 24 18 =SUM(A3:C3) 4 5 top 24 21 18 =SUM(A11:C11) 6 center 24 21 15 =SUM(A12:C12) 7 paper 24 21 12 =SUM(A13:C13) Basically, what I am doing is: 1.- Taking the sum for each row. 2.- Compare them with the number located in the cell G1. 3.- Find the row that match the number in the cell G1. 4.- Write in the cell H1 description of the row found. In my sample the selected row is number 5 which is cell G1=63 and cell H1 = top. Do you thing you can help me to get a macro to automate this procedure? Thanks in advance. Maperalia |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Automate Row Results.
Try this sub procedure on your active sheet
Sub Calculation() For i = 1 To 6 Range("F" & i + 1).Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])" Next i Range("H2").Select ActiveCell.FormulaR1C1 = "=INDEX(RC[-6]:R[6]C[-2],MATCH(RC[-1],RC [-2]:R[6]C[-2]),1)" End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Automate Row Results.
Abhijat;
Thanks for your quick response and the code. However, I ran it and the only row that is working properly is the "ROW 5". In others words the formula written in the code is just taking the text of the line 5. the rest do not match the text given in the the column "B2". Could you please tell me what I did wrong? Thanks. Maperalia "Abhijat" wrote: Try this sub procedure on your active sheet Sub Calculation() For i = 1 To 6 Range("F" & i + 1).Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])" Next i Range("H2").Select ActiveCell.FormulaR1C1 = "=INDEX(RC[-6]:R[6]C[-2],MATCH(RC[-1],RC [-2]:R[6]C[-2]),1)" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automate Macro | Excel Discussion (Misc queries) | |||
Automate Macro | Excel Discussion (Misc queries) | |||
Automate Autofilter Results - Copy to New Sheet | Excel Programming | |||
Automate grading of performance test results | Excel Worksheet Functions | |||
How can I list the results of my macro without overwritng previous results? | Excel Programming |