On May 14, 4:19*pm, Len wrote:
On May 14, 3:30*pm, OssieMac
wrote:





Hi Len,

Now I think that I understand. Either of the following examples ahould do
what you want.

Sub test()
Dim rng2 As Range
Range("A1").FormulaR1C1 = "=VALUE(RC[2])&RC[6]"
Set rng2 = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
Set rng2 = rng2.Offset(0, -1) 'Reset the range to offset to left
Cells(1, 1).Copy Destination:=rng2
End Sub

Sub test2()
Dim rng2 As Range
Range("A1").FormulaR1C1 = "=VALUE(RC[2])&RC[6]"
Set rng2 = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
'Following line is copy and paste in one line
Cells(1, 1).Copy Destination:=rng2.Offset(0, -1) *' Offset during paste
End Sub

--
Regards,

OssieMac

Hi ,

Great !.........your codes work

However, I need to try out another code suggested by FSt1

Thks & Regards
Len- Hide quoted text -

- Show quoted text -

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 ??

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

Thanks & Regards
Len