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

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" <(E-Mail Removed)> wrote in message
news:754413d2-dd12-48c4-9791-(E-Mail Removed)...
> 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
>
>

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" <ltong2000...@yahoo.co.uk> wrote in message
>
> news:754413d2-dd12-48c4-9791-(E-Mail Removed)...
>
>
>
> > 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

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

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