|
Macro
I need to create a macro that will do the following:
Sheet1 containg data (range A:J) In sheet2 I need macro to use ROW 1 Column A [ ="VICE" ] Column B [ ="I" ] Column C [ blank ] Column D [ =IF(Sheet1!I1="B","B1",IF(AND(Sheet1!I1="C",Sheet1 !J2="C*"),"C1","NIL")) ] Column E [ a given date ] Column F [ =Sheet1!F1 ] Column G [ ="CONTROL " &Sheet1!E1 ] Column H [ =0 ] ROW 2 Column A [ ="ACTION" ] Column B [ ="#" ] Column C ["#" ] Column D [ =Sheet2!F1 ] Column E [ =Sheet2!G1 ] Column F [ blank ] Column G [ blank ] Column H [ blank ] row 1 & 2 to repeat for the entire sheet1 that is in row 3 info fro sheet1 row 2 would be etracted. Sheet3 1A [ ="CONTROL " &Sheet1!E1 ] 2A [ blank ] 3A [ ="P " &Sheet1!B1 ] 4A [ ="P " &Sheet1!C1 ] 5A [ ="P " &Sheet1!G1 ] 6A [ ="P " &Sheet1!H1 ] 7A [ ="P " &Sheet1!A1 ] 8A TO RETREIVE INFO FROM Sheet1!E2 AND SO ON |
You really only need to key in the formulae you have already constructed in
Row 1 and 2 of Sheet 2. Then Hihlight the formulae in Row 2, go to the bottom right hand corner of the selection, click your mouse and drag it down as far as you need to to copy these formulae. The same for Sheet 3. Just get rid of the [ and ]. "titoto" wrote: I need to create a macro that will do the following: Sheet1 containg data (range A:J) In sheet2 I need macro to use ROW 1 Column A [ ="VICE" ] Column B [ ="I" ] Column C [ blank ] Column D [ =IF(Sheet1!I1="B","B1",IF(AND(Sheet1!I1="C",Sheet1 !J2="C*"),"C1","NIL")) ] Column E [ a given date ] Column F [ =Sheet1!F1 ] Column G [ ="CONTROL " &Sheet1!E1 ] Column H [ =0 ] ROW 2 Column A [ ="ACTION" ] Column B [ ="#" ] Column C ["#" ] Column D [ =Sheet2!F1 ] Column E [ =Sheet2!G1 ] Column F [ blank ] Column G [ blank ] Column H [ blank ] row 1 & 2 to repeat for the entire sheet1 that is in row 3 info fro sheet1 row 2 would be etracted. Sheet3 1A [ ="CONTROL " &Sheet1!E1 ] 2A [ blank ] 3A [ ="P " &Sheet1!B1 ] 4A [ ="P " &Sheet1!C1 ] 5A [ ="P " &Sheet1!G1 ] 6A [ ="P " &Sheet1!H1 ] 7A [ ="P " &Sheet1!A1 ] 8A TO RETREIVE INFO FROM Sheet1!E2 AND SO ON |
Kassie is not working
"Kassie" wrote: You really only need to key in the formulae you have already constructed in Row 1 and 2 of Sheet 2. Then Hihlight the formulae in Row 2, go to the bottom right hand corner of the selection, click your mouse and drag it down as far as you need to to copy these formulae. The same for Sheet 3. Just get rid of the [ and ]. "titoto" wrote: I need to create a macro that will do the following: Sheet1 containg data (range A:J) In sheet2 I need macro to use ROW 1 Column A [ ="VICE" ] Column B [ ="I" ] Column C [ blank ] Column D [ =IF(Sheet1!I1="B","B1",IF(AND(Sheet1!I1="C",Sheet1 !J2="C*"),"C1","NIL")) ] Column E [ a given date ] Column F [ =Sheet1!F1 ] Column G [ ="CONTROL " &Sheet1!E1 ] Column H [ =0 ] ROW 2 Column A [ ="ACTION" ] Column B [ ="#" ] Column C ["#" ] Column D [ =Sheet2!F1 ] Column E [ =Sheet2!G1 ] Column F [ blank ] Column G [ blank ] Column H [ blank ] row 1 & 2 to repeat for the entire sheet1 that is in row 3 info fro sheet1 row 2 would be etracted. Sheet3 1A [ ="CONTROL " &Sheet1!E1 ] 2A [ blank ] 3A [ ="P " &Sheet1!B1 ] 4A [ ="P " &Sheet1!C1 ] 5A [ ="P " &Sheet1!G1 ] 6A [ ="P " &Sheet1!H1 ] 7A [ ="P " &Sheet1!A1 ] 8A TO RETREIVE INFO FROM Sheet1!E2 AND SO ON |
I suggest you give me a cell by cell breakdown of data for Sheet 1 then. I
used the column A to H data you provided for row 1 and the formulae for row 2 in Sheet 2, as well as the formulae for Sheet 3, and it worked fine. Without your actual data, I cannot really assist you any further. "titoto" wrote: Kassie is not working "Kassie" wrote: You really only need to key in the formulae you have already constructed in Row 1 and 2 of Sheet 2. Then Hihlight the formulae in Row 2, go to the bottom right hand corner of the selection, click your mouse and drag it down as far as you need to to copy these formulae. The same for Sheet 3. Just get rid of the [ and ]. "titoto" wrote: I need to create a macro that will do the following: Sheet1 containg data (range A:J) In sheet2 I need macro to use ROW 1 Column A [ ="VICE" ] Column B [ ="I" ] Column C [ blank ] Column D [ =IF(Sheet1!I1="B","B1",IF(AND(Sheet1!I1="C",Sheet1 !J2="C*"),"C1","NIL")) ] Column E [ a given date ] Column F [ =Sheet1!F1 ] Column G [ ="CONTROL " &Sheet1!E1 ] Column H [ =0 ] ROW 2 Column A [ ="ACTION" ] Column B [ ="#" ] Column C ["#" ] Column D [ =Sheet2!F1 ] Column E [ =Sheet2!G1 ] Column F [ blank ] Column G [ blank ] Column H [ blank ] row 1 & 2 to repeat for the entire sheet1 that is in row 3 info fro sheet1 row 2 would be etracted. Sheet3 1A [ ="CONTROL " &Sheet1!E1 ] 2A [ blank ] 3A [ ="P " &Sheet1!B1 ] 4A [ ="P " &Sheet1!C1 ] 5A [ ="P " &Sheet1!G1 ] 6A [ ="P " &Sheet1!H1 ] 7A [ ="P " &Sheet1!A1 ] 8A TO RETREIVE INFO FROM Sheet1!E2 AND SO ON |
When run, it skip a row from Sheet1 e.g - it retrieves info A1 then A3, A5 --
also I need it to loop Thx for the help "Kassie" wrote: I suggest you give me a cell by cell breakdown of data for Sheet 1 then. I used the column A to H data you provided for row 1 and the formulae for row 2 in Sheet 2, as well as the formulae for Sheet 3, and it worked fine. Without your actual data, I cannot really assist you any further. "titoto" wrote: Kassie is not working "Kassie" wrote: You really only need to key in the formulae you have already constructed in Row 1 and 2 of Sheet 2. Then Hihlight the formulae in Row 2, go to the bottom right hand corner of the selection, click your mouse and drag it down as far as you need to to copy these formulae. The same for Sheet 3. Just get rid of the [ and ]. "titoto" wrote: I need to create a macro that will do the following: Sheet1 containg data (range A:J) In sheet2 I need macro to use ROW 1 Column A [ ="VICE" ] Column B [ ="I" ] Column C [ blank ] Column D [ =IF(Sheet1!I1="B","B1",IF(AND(Sheet1!I1="C",Sheet1 !J2="C*"),"C1","NIL")) ] Column E [ a given date ] Column F [ =Sheet1!F1 ] Column G [ ="CONTROL " &Sheet1!E1 ] Column H [ =0 ] ROW 2 Column A [ ="ACTION" ] Column B [ ="#" ] Column C ["#" ] Column D [ =Sheet2!F1 ] Column E [ =Sheet2!G1 ] Column F [ blank ] Column G [ blank ] Column H [ blank ] row 1 & 2 to repeat for the entire sheet1 that is in row 3 info fro sheet1 row 2 would be etracted. Sheet3 1A [ ="CONTROL " &Sheet1!E1 ] 2A [ blank ] 3A [ ="P " &Sheet1!B1 ] 4A [ ="P " &Sheet1!C1 ] 5A [ ="P " &Sheet1!G1 ] 6A [ ="P " &Sheet1!H1 ] 7A [ ="P " &Sheet1!A1 ] 8A TO RETREIVE INFO FROM Sheet1!E2 AND SO ON |
I suggest you read my comments interspersed with your original post.
Your problem is that you give me the desired results, but I have no knowledge of the source data where you get your results from. Then, I suggest you tell me what Sheet 1 contains. After that, What, and where in Sheet 2, do you want to achieve. In other words, sheet 1 Col A through Col J : give me the headings Sheet 2, What are the headings of the rows/columns Same for sheet 3 Now, What do you want to extract. eg Say in Sheet 2, Col A you want info from sheet 1, Col C, or whatever? "titoto" wrote: I need to create a macro that will do the following: the macro cannot add data, you will do that! Sheet1 containg data (range A:J) In sheet2 I need macro to use ROW 1 Is this now row 1 from Sheet 1, or from Sheet 2? Column A [ ="VICE" ] Column B [ ="I" ] Column C [ blank ] Column D [ =IF(Sheet1!I1="B","B1",IF(AND(Sheet1!I1="C",Sheet1 !J2="C*"),"C1","NIL")) ] Column E [ a given date ] Column F [ =Sheet1!F1 ] Column G [ ="CONTROL " &Sheet1!E1 ] Column H [ =0 ] ROW 2 Is this row 2 from Sheet 1 or 2? Column A [ ="ACTION" ] Column B [ ="#" ] Column C ["#" ] Column D [ =Sheet2!F1 ] Column E [ =Sheet2!G1 ] Column F [ blank ] Column G [ blank ] Column H [ blank ] row 1 & 2 to repeat for the entire sheet1 that is in row 3 info fro sheet1 row 2 would be etracted. You see, this is where you now totally confuse me! Are we in sheet 1 or 2? Sheet3 1A [ ="CONTROL " &Sheet1!E1 ] 2A [ blank ] 3A [ ="P " &Sheet1!B1 ] 4A [ ="P " &Sheet1!C1 ] 5A [ ="P " &Sheet1!G1 ] 6A [ ="P " &Sheet1!H1 ] 7A [ ="P " &Sheet1!A1 ] 8A TO RETREIVE INFO FROM Sheet1!E2 AND SO ON |
Kassie I hope the following is helpful in understanding what I am trying to
achieve Sheet1 contains A B C D E F G H I J 1 cpl Pri By type num loc Rm Ins PM olt 2 9/20/04 John A. 108379 5 N - YRT 5555 B IOT B 3 1/12/04 Marc R. 204464 85 W - NYJ 5555 DL IOT DL 4 9/23/04 Anna M. 199168 18 SW - KHG 5555 B IOT B 5 1/12/04 Andre Y. 156241 6 S - MVD 5555 C IOT C* 6 9/23/04 Lhea G. 189448 15 E - PLJ 5555 H IOT H 7 1/01/04 Bruce V. 107287 36 NE - CWA 5555 Y IOT Y Sheet2 should do A B C D E F G H I J 1 VICE I See below 3/22/05 Sheet1!F2 See below 0 2 ACTION 22 01338 =Sheet2!F1 =Sheet2!G1 3 VICE I see below 3/22/05 Sheet2!F3 See below 0 4 Same as 2 5 REAPET Row 1 D1 = IF(Sheet1!I2="B","B1",IF(AND(Sheet1!I2="C",Sheet1! J2="C*"),"C1","NIL G1 ="CONTROL " &Sheet1!E2 Row 3 D1 = IF(Sheet1!I3="B","B1",IF(AND(Sheet1!I3="C",Sheet1! J3="C*"),"C1","NIL G1 ="CONTROL " &Sheet1!E3 To repeat for each row of sheet1 that contains info. Sheet3 A 1 See below 2 blank 3 See below 4 See below 5 See below 6 See below 7 See below A1 ="CONTROL " &Sheet1!E2 A2 = blank ] A3 =Sheet1!$B$1&Sheet1!B2 A4 =Sheet1!$C$1&Sheet1!C2 A5 =Sheet1!$G$1&Sheet1!G2 A6 =Sheet1!$H$1&Sheet1!H2 A7 =Sheet1!$A$1&TEXT(Sheet1!A2,"mm/dd/yyyy") A8 TO REPEAT A2 €“ A7 FOR ALL INFO IN Sheet1! |
Thanks for the info. Will let you know soonest. Will be on 23/3
"titoto" wrote: Kassie I hope the following is helpful in understanding what I am trying to achieve Sheet1 contains A B C D E F G H I J 1 cpl Pri By type num loc Rm Ins PM olt 2 9/20/04 John A. 108379 5 N - YRT 5555 B IOT B 3 1/12/04 Marc R. 204464 85 W - NYJ 5555 DL IOT DL 4 9/23/04 Anna M. 199168 18 SW - KHG 5555 B IOT B 5 1/12/04 Andre Y. 156241 6 S - MVD 5555 C IOT C* 6 9/23/04 Lhea G. 189448 15 E - PLJ 5555 H IOT H 7 1/01/04 Bruce V. 107287 36 NE - CWA 5555 Y IOT Y Sheet2 should do A B C D E F G H I J 1 VICE I See below 3/22/05 Sheet1!F2 See below 0 2 ACTION 22 01338 =Sheet2!F1 =Sheet2!G1 3 VICE I see below 3/22/05 Sheet2!F3 See below 0 4 Same as 2 5 REAPET Row 1 D1 = IF(Sheet1!I2="B","B1",IF(AND(Sheet1!I2="C",Sheet1! J2="C*"),"C1","NIL G1 ="CONTROL " &Sheet1!E2 Row 3 D1 = IF(Sheet1!I3="B","B1",IF(AND(Sheet1!I3="C",Sheet1! J3="C*"),"C1","NIL G1 ="CONTROL " &Sheet1!E3 To repeat for each row of sheet1 that contains info. Sheet3 A 1 See below 2 blank 3 See below 4 See below 5 See below 6 See below 7 See below A1 ="CONTROL " &Sheet1!E2 A2 = blank ] A3 =Sheet1!$B$1&Sheet1!B2 A4 =Sheet1!$C$1&Sheet1!C2 A5 =Sheet1!$G$1&Sheet1!G2 A6 =Sheet1!$H$1&Sheet1!H2 A7 =Sheet1!$A$1&TEXT(Sheet1!A2,"mm/dd/yyyy") A8 TO REPEAT A2 €“ A7 FOR ALL INFO IN Sheet1! |
You wrote
3 VICE I see below 3/22/05 Sheet2!F3 See below 0 The way I understand this is: Col A: VICE Col B: I Col C:Blank Col D: See below Col E: 3/22/05 Col F: = Sheet2!F3? This creates a circular reference Do you perhaps mean =Sheet1!F3? Please advise Row 1 D1 = IF(Sheet1!I2="B","B1",IF(AND(Sheet1!I2="C",Sheet1! J2="C*"),"C1","NIL G1 ="CONTROL " &Sheet1!E2 Row 3 D1 = IF(Sheet1!I3="B","B1",IF(AND(Sheet1!I3="C",Sheet1! J3="C*"),"C1","NIL G1 ="CONTROL " &Sheet1!E3 To repeat for each row of sheet1 that contains info. Sheet3 A 1 See below 2 blank 3 See below 4 See below 5 See below 6 See below 7 See below A1 ="CONTROL " &Sheet1!E2 A2 = blank ] A3 =Sheet1!$B$1&Sheet1!B2 A4 =Sheet1!$C$1&Sheet1!C2 A5 =Sheet1!$G$1&Sheet1!G2 A6 =Sheet1!$H$1&Sheet1!H2 A7 =Sheet1!$A$1&TEXT(Sheet1!A2,"mm/dd/yyyy") A8 TO REPEAT A2 €“ A7 FOR ALL INFO IN Sheet1! |
Sheet2
Row1 Col A: VICE Col B: I Col C:Blank Col D: IF(Sheet1!I2="B","B1",IF(AND(Sheet1!I2="C",Sheet1! J2="C*"),"C1","NIL") Col E: 3/22/05 Col F:Sheet1!F2 Col G:"CONTROL " &Sheet1!E2 Col H: 0 Row2 Col A: CONTROL Col B: 22 Col C:'01338 Col D:Sheet2!F1 Col E:Sheet2!G1 Row3 -- X repeat formula in row1 for (Sheet1! Row2) SHEET3 A1:"CONTROL " &Sheet1!E2 A2: blank A3:Sheet1!$B$1&Sheet1!B2 A4:Sheet1!$C$1&Sheet1!C2 A5:Sheet1!$G$1&Sheet1!G2 A6:Sheet1!$H$1&Sheet1!H2 A7:Sheet1!$A$1&TEXT(Sheet1!A2,"mm/dd/yyyy") A8: TO REPEAT A2 €“ A7 FOR ALL INFO IN Sheet1! |
Thanks Titoto. Makes a lot more sense now. I will work on it, and let you
know! "titoto" wrote: Sheet2 Row1 Col A: VICE Col B: I Col C:Blank Col D: IF(Sheet1!I2="B","B1",IF(AND(Sheet1!I2="C",Sheet1! J2="C*"),"C1","NIL") Col E: 3/22/05 Col F:Sheet1!F2 Col G:"CONTROL " &Sheet1!E2 Col H: 0 Row2 Col A: CONTROL Col B: 22 Col C:'01338 Col D:Sheet2!F1 Col E:Sheet2!G1 Row3 -- X repeat formula in row1 for (Sheet1! Row2) SHEET3 A1:"CONTROL " &Sheet1!E2 A2: blank A3:Sheet1!$B$1&Sheet1!B2 A4:Sheet1!$C$1&Sheet1!C2 A5:Sheet1!$G$1&Sheet1!G2 A6:Sheet1!$H$1&Sheet1!H2 A7:Sheet1!$A$1&TEXT(Sheet1!A2,"mm/dd/yyyy") A8: TO REPEAT A2 €“ A7 FOR ALL INFO IN Sheet1! |
Hi
I took it that you only want to copy the formule, especially on Sheet 2. If not, you can easily add in the other cells. In your worksheet, press <Alt<F11, insert a module, and copy the following: Dim lCnt As Long Dim mCnt As Long Dim iRow As Long Dim tCnt As Long Sub Starting() Worksheets("Sheet1").Activate iRow = 1 While Sheet1.Range("A" & iRow).Value < "" iRow = iRow + 1 Wend Worksheets("Sheet2").Activate lCnt = 2 mCnt = 1 Insertion End Sub Sub Insertion() lRow = 8 lCnt = 2 mCnt = 1 tCnt = 1 While lCnt <= lRow Range("A" & mCnt).Select ActiveCell.Offset(0, 3).Select ActiveCell.Formula = "=IF(Sheet1!I2=""B"",""B1"",IF(AND(Sheet1!I2=""C"" ,Sheet1!J2=""C*""),""C1"",""NIL""))" ActiveCell.Offset(0, 2).Select ActiveCell.Formula = "=Sheet1!F" & lCnt ActiveCell.Offset(0, 1).Select ActiveCell.Formula = "=""CONTROL "" & Sheet1!E" & lCnt ActiveCell.Offset(1, -3).Select ActiveCellFormula = "=Sheet2!F" & mCnt ActiveCell.Offset(0, 1).Select ActiveCell.Formula = "=Sheet2!G" & mCnt Worksheets("Sheet3").Activate Range("A" & tCnt).Select ActiveCell.Formula = "=""CONTROL "" & Sheet1!E" & lCnt ActiveCell.Offset(2, 0).Select mForm = "=Sheet1!$B$" & mCnt & "&Sheet1!B" & lCnt ActiveCell.Formula = mForm mForm = "" ActiveCell.Offset(1, 0).Select mForm = "=Sheet1!$C$" & mCnt & "&Sheet1!C" & lCnt ActiveCell.Formula = mForm mForm = "" ActiveCell.Offset(1, 0).Select mForm = "=Sheet1!$G$" & mCnt & "&Sheet1!G" & lCnt ActiveCell.Formula = mForm mForm = "" ActiveCell.Offset(1, 0).Select mForm = "=Sheet1!$H$" & mCnt & "&Sheet1!H" & lCnt ActiveCell.Formula = mForm mForm = "" ActiveCell.Offset(1, 0).Select mForm = "=Sheet1!$A$" & mCnt & "&TEXT(Sheet1!A" & mCnt & ",""mm/dd/yyyy"")" ActiveCell.Formula = mForm mForm = "" Worksheets("Sheet2").Activate lCnt = lCnt + 1 mCnt = mCnt + 1 tCnt = tCnt + iRow - 1 Wend End Sub "titoto" wrote: I need to create a macro that will do the following: Sheet1 containg data (range A:J) In sheet2 I need macro to use ROW 1 Column A [ ="VICE" ] Column B [ ="I" ] Column C [ blank ] Column D [ =IF(Sheet1!I1="B","B1",IF(AND(Sheet1!I1="C",Sheet1 !J2="C*"),"C1","NIL")) ] Column E [ a given date ] Column F [ =Sheet1!F1 ] Column G [ ="CONTROL " &Sheet1!E1 ] Column H [ =0 ] ROW 2 Column A [ ="ACTION" ] Column B [ ="#" ] Column C ["#" ] Column D [ =Sheet2!F1 ] Column E [ =Sheet2!G1 ] Column F [ blank ] Column G [ blank ] Column H [ blank ] row 1 & 2 to repeat for the entire sheet1 that is in row 3 info fro sheet1 row 2 would be etracted. Sheet3 1A [ ="CONTROL " &Sheet1!E1 ] 2A [ blank ] 3A [ ="P " &Sheet1!B1 ] 4A [ ="P " &Sheet1!C1 ] 5A [ ="P " &Sheet1!G1 ] 6A [ ="P " &Sheet1!H1 ] 7A [ ="P " &Sheet1!A1 ] 8A TO RETREIVE INFO FROM Sheet1!E2 AND SO ON |
It is not running - Error on
Sub Insertion() lRow = 8 Thanks for your help. I notice you have answered my IF problem - It was ressolved but I'll try yours anyway "Kassie" wrote: Hi I took it that you only want to copy the formule, especially on Sheet 2. If not, you can easily add in the other cells. In your worksheet, press <Alt<F11, insert a module, and copy the following: Dim lCnt As Long Dim mCnt As Long Dim iRow As Long Dim tCnt As Long Sub Starting() Worksheets("Sheet1").Activate iRow = 1 While Sheet1.Range("A" & iRow).Value < "" iRow = iRow + 1 Wend Worksheets("Sheet2").Activate lCnt = 2 mCnt = 1 Insertion End Sub Sub Insertion() lRow = 8 lCnt = 2 mCnt = 1 tCnt = 1 While lCnt <= lRow Range("A" & mCnt).Select ActiveCell.Offset(0, 3).Select ActiveCell.Formula = "=IF(Sheet1!I2=""B"",""B1"",IF(AND(Sheet1!I2=""C"" ,Sheet1!J2=""C*""),""C1"",""NIL""))" ActiveCell.Offset(0, 2).Select ActiveCell.Formula = "=Sheet1!F" & lCnt ActiveCell.Offset(0, 1).Select ActiveCell.Formula = "=""CONTROL "" & Sheet1!E" & lCnt ActiveCell.Offset(1, -3).Select ActiveCellFormula = "=Sheet2!F" & mCnt ActiveCell.Offset(0, 1).Select ActiveCell.Formula = "=Sheet2!G" & mCnt Worksheets("Sheet3").Activate Range("A" & tCnt).Select ActiveCell.Formula = "=""CONTROL "" & Sheet1!E" & lCnt ActiveCell.Offset(2, 0).Select mForm = "=Sheet1!$B$" & mCnt & "&Sheet1!B" & lCnt ActiveCell.Formula = mForm mForm = "" ActiveCell.Offset(1, 0).Select mForm = "=Sheet1!$C$" & mCnt & "&Sheet1!C" & lCnt ActiveCell.Formula = mForm mForm = "" ActiveCell.Offset(1, 0).Select mForm = "=Sheet1!$G$" & mCnt & "&Sheet1!G" & lCnt ActiveCell.Formula = mForm mForm = "" ActiveCell.Offset(1, 0).Select mForm = "=Sheet1!$H$" & mCnt & "&Sheet1!H" & lCnt ActiveCell.Formula = mForm mForm = "" ActiveCell.Offset(1, 0).Select mForm = "=Sheet1!$A$" & mCnt & "&TEXT(Sheet1!A" & mCnt & ",""mm/dd/yyyy"")" ActiveCell.Formula = mForm mForm = "" Worksheets("Sheet2").Activate lCnt = lCnt + 1 mCnt = mCnt + 1 tCnt = tCnt + iRow - 1 Wend End Sub "titoto" wrote: I need to create a macro that will do the following: Sheet1 containg data (range A:J) In sheet2 I need macro to use ROW 1 Column A [ ="VICE" ] Column B [ ="I" ] Column C [ blank ] Column D [ =IF(Sheet1!I1="B","B1",IF(AND(Sheet1!I1="C",Sheet1 !J2="C*"),"C1","NIL")) ] Column E [ a given date ] Column F [ =Sheet1!F1 ] Column G [ ="CONTROL " &Sheet1!E1 ] Column H [ =0 ] ROW 2 Column A [ ="ACTION" ] Column B [ ="#" ] Column C ["#" ] Column D [ =Sheet2!F1 ] Column E [ =Sheet2!G1 ] Column F [ blank ] Column G [ blank ] Column H [ blank ] row 1 & 2 to repeat for the entire sheet1 that is in row 3 info fro sheet1 row 2 would be etracted. Sheet3 1A [ ="CONTROL " &Sheet1!E1 ] 2A [ blank ] 3A [ ="P " &Sheet1!B1 ] 4A [ ="P " &Sheet1!C1 ] 5A [ ="P " &Sheet1!G1 ] 6A [ ="P " &Sheet1!H1 ] 7A [ ="P " &Sheet1!A1 ] 8A TO RETREIVE INFO FROM Sheet1!E2 AND SO ON |
I did not notice info for the for Col A - C
If you have this macro saved, could forward to me an attached file. Sheet2 Row1 Col A: VICE Col B: I Col C:Blank Col D: IF(Sheet1!I2="B","B1",IF(AND(Sheet1!I2="C",Sheet1! J2="C*"),"C1","NIL") Col E: 3/22/05 Col F:Sheet1!F2 Col G:"CONTROL " &Sheet1!E2 Col H: 0 Row2 Col A: CONTROL Col B: 22 Col C:'01338 Col D:Sheet2!F1 Col E:Sheet2!G1 "Kassie" wrote: Hi I took it that you only want to copy the formule, especially on Sheet 2. If not, you can easily add in the other cells. In your worksheet, press <Alt<F11, insert a module, and copy the following: Dim lCnt As Long Dim mCnt As Long Dim iRow As Long Dim tCnt As Long Sub Starting() Worksheets("Sheet1").Activate iRow = 1 While Sheet1.Range("A" & iRow).Value < "" iRow = iRow + 1 Wend Worksheets("Sheet2").Activate lCnt = 2 mCnt = 1 Insertion End Sub Sub Insertion() lRow = 8 lCnt = 2 mCnt = 1 tCnt = 1 While lCnt <= lRow Range("A" & mCnt).Select ActiveCell.Offset(0, 3).Select ActiveCell.Formula = "=IF(Sheet1!I2=""B"",""B1"",IF(AND(Sheet1!I2=""C"" ,Sheet1!J2=""C*""),""C1"",""NIL""))" ActiveCell.Offset(0, 2).Select ActiveCell.Formula = "=Sheet1!F" & lCnt ActiveCell.Offset(0, 1).Select ActiveCell.Formula = "=""CONTROL "" & Sheet1!E" & lCnt ActiveCell.Offset(1, -3).Select ActiveCellFormula = "=Sheet2!F" & mCnt ActiveCell.Offset(0, 1).Select ActiveCell.Formula = "=Sheet2!G" & mCnt Worksheets("Sheet3").Activate Range("A" & tCnt).Select ActiveCell.Formula = "=""CONTROL "" & Sheet1!E" & lCnt ActiveCell.Offset(2, 0).Select mForm = "=Sheet1!$B$" & mCnt & "&Sheet1!B" & lCnt ActiveCell.Formula = mForm mForm = "" ActiveCell.Offset(1, 0).Select mForm = "=Sheet1!$C$" & mCnt & "&Sheet1!C" & lCnt ActiveCell.Formula = mForm mForm = "" ActiveCell.Offset(1, 0).Select mForm = "=Sheet1!$G$" & mCnt & "&Sheet1!G" & lCnt ActiveCell.Formula = mForm mForm = "" ActiveCell.Offset(1, 0).Select mForm = "=Sheet1!$H$" & mCnt & "&Sheet1!H" & lCnt ActiveCell.Formula = mForm mForm = "" ActiveCell.Offset(1, 0).Select mForm = "=Sheet1!$A$" & mCnt & "&TEXT(Sheet1!A" & mCnt & ",""mm/dd/yyyy"")" ActiveCell.Formula = mForm mForm = "" Worksheets("Sheet2").Activate lCnt = lCnt + 1 mCnt = mCnt + 1 tCnt = tCnt + iRow - 1 Wend End Sub "titoto" wrote: I need to create a macro that will do the following: Sheet1 containg data (range A:J) In sheet2 I need macro to use ROW 1 Column A [ ="VICE" ] Column B [ ="I" ] Column C [ blank ] Column D [ =IF(Sheet1!I1="B","B1",IF(AND(Sheet1!I1="C",Sheet1 !J2="C*"),"C1","NIL")) ] Column E [ a given date ] Column F [ =Sheet1!F1 ] Column G [ ="CONTROL " &Sheet1!E1 ] Column H [ =0 ] ROW 2 Column A [ ="ACTION" ] Column B [ ="#" ] Column C ["#" ] Column D [ =Sheet2!F1 ] Column E [ =Sheet2!G1 ] Column F [ blank ] Column G [ blank ] Column H [ blank ] row 1 & 2 to repeat for the entire sheet1 that is in row 3 info fro sheet1 row 2 would be etracted. Sheet3 1A [ ="CONTROL " &Sheet1!E1 ] 2A [ blank ] 3A [ ="P " &Sheet1!B1 ] 4A [ ="P " &Sheet1!C1 ] 5A [ ="P " &Sheet1!G1 ] 6A [ ="P " &Sheet1!H1 ] 7A [ ="P " &Sheet1!A1 ] 8A TO RETREIVE INFO FROM Sheet1!E2 AND SO ON |
Hi
I took it that Col A - C would be input by yourself. If you want to adapt the macro. that would be very easy to do. I have saved the file, so I can send it to you. Post your mail address, and I will do so "titoto" wrote: I did not notice info for the for Col A - C If you have this macro saved, could forward to me an attached file. Sheet2 Row1 Col A: VICE Col B: I Col C:Blank Col D: IF(Sheet1!I2="B","B1",IF(AND(Sheet1!I2="C",Sheet1! J2="C*"),"C1","NIL") Col E: 3/22/05 Col F:Sheet1!F2 Col G:"CONTROL " &Sheet1!E2 Col H: 0 Row2 Col A: CONTROL Col B: 22 Col C:'01338 Col D:Sheet2!F1 Col E:Sheet2!G1 "Kassie" wrote: Hi I took it that you only want to copy the formule, especially on Sheet 2. If not, you can easily add in the other cells. In your worksheet, press <Alt<F11, insert a module, and copy the following: Dim lCnt As Long Dim mCnt As Long Dim iRow As Long Dim tCnt As Long Sub Starting() Worksheets("Sheet1").Activate iRow = 1 While Sheet1.Range("A" & iRow).Value < "" iRow = iRow + 1 Wend Worksheets("Sheet2").Activate lCnt = 2 mCnt = 1 Insertion End Sub Sub Insertion() lRow = 8 lCnt = 2 mCnt = 1 tCnt = 1 While lCnt <= lRow Range("A" & mCnt).Select ActiveCell.Offset(0, 3).Select ActiveCell.Formula = "=IF(Sheet1!I2=""B"",""B1"",IF(AND(Sheet1!I2=""C"" ,Sheet1!J2=""C*""),""C1"",""NIL""))" ActiveCell.Offset(0, 2).Select ActiveCell.Formula = "=Sheet1!F" & lCnt ActiveCell.Offset(0, 1).Select ActiveCell.Formula = "=""CONTROL "" & Sheet1!E" & lCnt ActiveCell.Offset(1, -3).Select ActiveCellFormula = "=Sheet2!F" & mCnt ActiveCell.Offset(0, 1).Select ActiveCell.Formula = "=Sheet2!G" & mCnt Worksheets("Sheet3").Activate Range("A" & tCnt).Select ActiveCell.Formula = "=""CONTROL "" & Sheet1!E" & lCnt ActiveCell.Offset(2, 0).Select mForm = "=Sheet1!$B$" & mCnt & "&Sheet1!B" & lCnt ActiveCell.Formula = mForm mForm = "" ActiveCell.Offset(1, 0).Select mForm = "=Sheet1!$C$" & mCnt & "&Sheet1!C" & lCnt ActiveCell.Formula = mForm mForm = "" ActiveCell.Offset(1, 0).Select mForm = "=Sheet1!$G$" & mCnt & "&Sheet1!G" & lCnt ActiveCell.Formula = mForm mForm = "" ActiveCell.Offset(1, 0).Select mForm = "=Sheet1!$H$" & mCnt & "&Sheet1!H" & lCnt ActiveCell.Formula = mForm mForm = "" ActiveCell.Offset(1, 0).Select mForm = "=Sheet1!$A$" & mCnt & "&TEXT(Sheet1!A" & mCnt & ",""mm/dd/yyyy"")" ActiveCell.Formula = mForm mForm = "" Worksheets("Sheet2").Activate lCnt = lCnt + 1 mCnt = mCnt + 1 tCnt = tCnt + iRow - 1 Wend End Sub "titoto" wrote: I need to create a macro that will do the following: Sheet1 containg data (range A:J) In sheet2 I need macro to use ROW 1 Column A [ ="VICE" ] Column B [ ="I" ] Column C [ blank ] Column D [ =IF(Sheet1!I1="B","B1",IF(AND(Sheet1!I1="C",Sheet1 !J2="C*"),"C1","NIL")) ] Column E [ a given date ] Column F [ =Sheet1!F1 ] Column G [ ="CONTROL " &Sheet1!E1 ] Column H [ =0 ] ROW 2 Column A [ ="ACTION" ] Column B [ ="#" ] Column C ["#" ] Column D [ =Sheet2!F1 ] Column E [ =Sheet2!G1 ] Column F [ blank ] Column G [ blank ] Column H [ blank ] row 1 & 2 to repeat for the entire sheet1 that is in row 3 info fro sheet1 row 2 would be etracted. Sheet3 1A [ ="CONTROL " &Sheet1!E1 ] 2A [ blank ] 3A [ ="P " &Sheet1!B1 ] 4A [ ="P " &Sheet1!C1 ] 5A [ ="P " &Sheet1!G1 ] 6A [ ="P " &Sheet1!H1 ] 7A [ ="P " &Sheet1!A1 ] 8A TO RETREIVE INFO FROM Sheet1!E2 AND SO ON |
|
| All times are GMT +1. The time now is 02:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com