Auto drag and fill formula.........................

Posted by --------------------------Ian on September 26, 2001 12:07 AM

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 drag & fill down 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 on sheet1. Is it possible to make a macro or VBA for this? Thanks!

Posted by Ian on September 26, 2001 2:34 AM

Sheet 2

Posted by Robb on September 26, 2001 4:24 AM

Ian

This is not particularly elegant, but it should do what you seem to be wanting.

I assumed you would fill values in A4 to E4 at the same time, so it works when A4 is entered.

Replace the earlier code with this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Worksheets("Sheet1")
If Not Application.Intersect(Target, .Columns(1)) Is Nothing Then
Dim ad As String, a As Integer, rg As String
If Target.Row = 1 Then GoTo skip
ad = Target.Address(rowabsolute:=False, columnabsolute:=False)
cp = Target.Offset(-1, 0).Address(rowabsolute:=False, columnabsolute:=False)
a = (Target.Row - 1)
rg = cp & ":EZ" & CStr(a)
Worksheets("Sheet2").Range(rg).Copy Destination:=Worksheets("Sheet2").Range(ad)
Target.Offset(1, 0).Select
Else
End If
End With
skip:
End Sub

Does that do the trick?

Regards

Posted by Robb on September 26, 2001 4:30 AM

Ian

It occurred to me that, since you will be entering values in a row,
it may be easier for you if the next cell in the row activates after
A. Use this instead:

Hope it helps.

Regards

: The following is an example of the workbook that I intent to make, : ...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 : ...........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..

Posted by Ian on September 26, 2001 4:10 PM

It works wonderfully, Thanks so much

Wow, it works, thank yu Robb

best regards,

Posted by -----------------------------------------------------------------Ian on September 27, 2001 4:19 PM

what if I use copy and paste.........................

Robb,

If i were to use copy and paste to the range in sheet1 for a no. of rows, the VB yu gave me could not work, it works only when i type in values, How to make sheet2 formula auto fill down when there is a number greater than 0 is entered or pasted in sheet1, and value that equals 0 should not be drag and filled? Thanks

Ian it may be easier for you if the next cell in the row activates after A. Use this instead: Target.Offset(0, 1).Select : Ian : This is not particularly elegant, but it should do what you seem to be wanting. : I assumed you would fill values in A4 to E4 at the same time, so it works when A4 is entered. : Replace the earlier code with this