Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Array Formula
Hi ,
It seem that the modified VBA code ( ie suggested by OssieMac ) below can not work when it copies down excel array formula for this scenario, does it miss out any code ?? Please help, thanks Sub test() Dim rng2 As Range Range("J1").FormulaArray = "=IF(ISERROR(G1-H1),G1,(G1-H1))" Set rng2 = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp)) Set rng2 = rng2.Offset(0, 8) Cells(1, 10).Copy Destination:=rng2 End Sub Regards Len |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Array Formula
LEn,
That formula will error out if you try to copy it to column B, and your syntax is wrong, anyway. Try it like this to match column H: Dim rng2 As Range Range("J1").FormulaArray = "=IF(ISERROR(G1-H1),G1,(G1-H1))" Set rng2 = Range("J2", Cells(Rows.Count, 8).End(xlUp).Offset(1, 2)) MsgBox rng2.Address Range("J1").Copy rng2 HTH, Bernie MS Excel MVP "Len" wrote in message ... Hi , It seem that the modified VBA code ( ie suggested by OssieMac ) below can not work when it copies down excel array formula for this scenario, does it miss out any code ?? Please help, thanks Sub test() Dim rng2 As Range Range("J1").FormulaArray = "=IF(ISERROR(G1-H1),G1,(G1-H1))" Set rng2 = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp)) Set rng2 = rng2.Offset(0, 8) Cells(1, 10).Copy Destination:=rng2 End Sub Regards Len |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Array Formula
On May 15, 10:33*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: LEn, That formula will error out if you try to copy it to column B, and your syntax is wrong, anyway. Try it like this to match column H: Dim rng2 As Range Range("J1").FormulaArray = "=IF(ISERROR(G1-H1),G1,(G1-H1))" Set rng2 = Range("J2", Cells(Rows.Count, 8).End(xlUp).Offset(1, 2)) MsgBox rng2.Address Range("J1").Copy rng2 HTH, Bernie MS Excel MVP "Len" wrote in message ... Hi , It seem that the modified VBA code ( ie suggested by OssieMac ) below can not work when it copies down excel array formula for this scenario, does it miss out any code ?? Please help, thanks Sub test() Dim rng2 As Range Range("J1").FormulaArray = "=IF(ISERROR(G1-H1),G1,(G1-H1))" Set rng2 = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp)) Set rng2 = rng2.Offset(0, 8) Cells(1, 10).Copy Destination:=rng2 End Sub Regards Len- Hide quoted text - - Show quoted text - Hi Bernie, Thanks for your code but this VBA code has to copy down the excel array formula ( ie from J1 ) in column J depending on the last used cells of column B, then how to rectify the code Regards Len |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Array Formula
Len
Dim rng2 As Range Range("J1").FormulaArray = "=IF(ISERROR(G1-H1),G1,(G1-H1))" Set rng2 = Range("J2", Cells(Rows.Count, 2).End(xlUp).Offset(1, 8)) MsgBox rng2.Address Range("J1").Copy rng2 You can take out the Msgbox line... HTH, Bernie MS Excel MVP Hi Bernie, Thanks for your code but this VBA code has to copy down the excel array formula ( ie from J1 ) in column J depending on the last used cells of column B, then how to rectify the code Regards Len |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Array Formula
On May 15, 7:00*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: Len Dim rng2 As Range Range("J1").FormulaArray = "=IF(ISERROR(G1-H1),G1,(G1-H1))" Set rng2 = Range("J2", Cells(Rows.Count, 2).End(xlUp).Offset(1, 8)) MsgBox rng2.Address Range("J1").Copy rng2 You can take out the Msgbox line... HTH, Bernie MS Excel MVP Hi Bernie, Thanks for your code but this VBA code has to copy down the excel array formula ( ie from J1 ) in column J depending on the last used cells of column B, then how to rectify the code Regards Len Hi Bernie, Sorry, I was away and unable to access pc for almost a week. Thanks, your codes work Regards Len |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy one array formula to an array range | Excel Programming | |||
How do I copy an array formula? | Excel Discussion (Misc queries) | |||
copy array formula | Excel Worksheet Functions | |||
copy an array formula | Excel Programming | |||
Copy An Array Formula | Excel Discussion (Misc queries) |