Macro

G

Guest

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
 
G

Guest

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 ].
 
G

Guest

Kassie is not working

Kassie said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

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 said:
Kassie is not working

Kassie said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
Kassie is not working

Kassie said:
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 ].

:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
I need to create a macro that will do the following:
Click to expand...
the macro cannot add data, you will do that!
Sheet1 containg data (range A:J)
In sheet2 I need macro to use
ROW 1
Click to expand...

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
Click to expand...

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.
Click to expand...

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
Click to expand...
 
G

Guest

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!
 
G

Guest

You wrote
3 VICE I see below 3/22/05 Sheet2!F3 See below 0
Click to expand...
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!
Click to expand...
 
G

Guest

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!
 
G

Guest

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
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Kassie here is my e-mail
(e-mail address removed)

thanks
Kassie said:
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 said:
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 said:
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


:

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
Click to expand...
Click to expand...
Click to expand...
 

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

Need help with a formula, please!!! 2
bookkeeping-payment confirmation 7
List to a table format 6
Formula Question 2
Display data from another worksheet 2
VLOOKUP Addresses for Invoice 1
SUM of a row of formulas 13
Automatic populate Sheet2 with data from Sheet1 based on criteria. 2

Top