Copy formula
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
|