MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Hello Robb, bother yu again.......


Posted by -------------------Ian on September 24, 2001 7:19 PM

Thanks Robb, it works for the column A of sheet 2, how to make the column B to EZ auto-formulated itself too?
Sorry that i am not good at programming.

You really are a genius!

Ian

I think you'll need to use code in Sheet1. Try this, it should complete
the formulae down columnA

- Right click on the page tab for Sheet1
- Select View Code
- Paste this code in the code window

It will copy any formula from A1 in Sheet2 to A2 if you enter something
in Sheet1 A2 and so on down the column.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Worksheets("Sheet1")
If Not Application.Intersect(Target, .Columns(1)) Is Nothing Then
ad = Target.Address
If ad = "$A$1" Then GoTo skip
Worksheets("Sheet2").Range(ad).Offset(-1, 0).Copy Destination:=Worksheets("Sheet2").Range(ad)
Else
End If
End With
skip:
End Sub

Any help?

Regards



Posted by Ian on September 24, 2001 7:26 PM

Thanks Robb, it works for the column A of sheet 2, now I want to type a range of value from A2 to E2 of sheet 1, and these data should auto-formulated to sheet2 from column B2 to EZ2 range, How to do this?
Sorry that i am not good at programming.


Posted by Robb on September 25, 2001 4:44 AM

Ian

Not sure whether you want to have the same formula in all the cells or if you want
to put something different in the rest of the cells in Row1. Also not sure whether
you would be filling the columns in order. Anyway, copy this in place of the previous
code - it should populate the same code in all cells in the range in any order you choose to fill them.
In other words Sheet2.EZ1 will contain =Sheet1.EZ1 etc.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Worksheets("Sheet1")
If Not Application.Intersect(Target, .Range(.Columns(1), .Columns(156))) Is Nothing Then
ad = Target.Address
Worksheets("Sheet2").Cells(1, 1).Copy Destination:=Worksheets("Sheet2").Range(ad)
Target.Offset(1, 0).Select
Else
End If
End With
skip:
End Sub

Any good for you?

Regards

Posted by --------------------Ian on September 25, 2001 5:15 PM

Thanks, and what if......................

Thanks, the following is an example of the workbook that I intent to make,

Sheet 1
A B C D E
1 100 200 300 400 500
2 200 300 400 500 600
3 300 400 500 600 700
4 ¡K¡K¡K¡K¡K¡K¡K¡K¡K

Sheet 2
A B EZ
1 =Sheet1!A1*Sheet1!B1 =Sheet1!A1*Sheet1!B1/Sheet1!E1 =A1+B1/E1
2 =Sheet1!A2*Sheet1!B2 =Sheet1!A2*Sheet1!B2/Sheet1!E2 =A2+B2/E2
3 =Sheet1!A3*Sheet1!B3 =Sheet1!A3*Sheet1!B3/Sheet1!E3 =A3+B3/E3
4 ¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K..

I want it as when I type in values at Sheet1 A4 B4 C4 D4 E4, the formula on sheet2 A4 ~ EZ4 would auto fill the formula format. Sheet1 has only 5 columns of data, and Sheet2 has 156 columns of formula data (A to EZ) which are all related to the 5 column data of sheet1. Is it possible to make a macro for this? Thanks again!

Posted by ---------Ian on September 25, 2001 5:18 PM

Thanks, the following is an example of the workbook that I intent to make,

Sheet 1
A B C D E
1 100 200 300 400 500
2 200 300 400 500 600
3 300 400 500 600 700
4 ¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K

Sheet 2
A B EZ
1 =Sheet1!A1*Sheet1!B1 =Sheet1!A1*Sheet1!B1/Sheet1!E1 =A1+B1/E1
2 =Sheet1!A2*Sheet1!B2 =Sheet1!A2*Sheet1!B2/Sheet1!E2 =A2+B2/E2
3 =Sheet1!A3*Sheet1!B3 =Sheet1!A3*Sheet1!B3/Sheet1!E3 =A3+B3/E3
4 ¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K

I want it as when I type in values at Sheet1 A4 B4 C4 D4 E4, the formula on sheet2 A4 ~ EZ4 would auto fill the formula format. Sheet1 has only 5 columns of data, and Sheet2 has 156 columns of formula data (A to EZ) which are all related to the 5 column data of sheet1. Is it possible to make a macro for this? Thanks again!

Posted by ---------Ian on September 25, 2001 5:19 PM

Thanks, the following is an example of the workbook that I intent to make,

Sheet 1
A B C D E
1 100 200 300 400 500
2 200 300 400 500 600
3 300 400 500 600 700
4 ¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K

Sheet 2
A B EZ
1 =Sheet1!A1*Sheet1!B1 =Sheet1!A1*Sheet1!B1/Sheet1!E1 =A1+B1/E1
2 =Sheet1!A2*Sheet1!B2 =Sheet1!A2*Sheet1!B2/Sheet1!E2 =A2+B2/E2
3 =Sheet1!A3*Sheet1!B3 =Sheet1!A3*Sheet1!B3/Sheet1!E3 =A3+B3/E3
4 ¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K¡K

I want it as when I type in values at Sheet1 A4 B4 C4 D4 E4, the formula on sheet2 A4 ~ EZ4 would auto fill the formula format. Sheet1 has only 5 columns of data, and Sheet2 has 156 columns of formula data (A to EZ) which are all related to the 5 column data of sheet1. Is it possible to make a macro for this? Thanks again!