Copy formula

[Len]

Hi,

I have this problem when I run excel macro below on a new worksheet
where there is no data except the header on row1, then the excel
formula still copying down the whole column A with 0 value and it will
stop copying until the last used cell if there is data on column A
How can I modify this excel macro to decide whether to copy the excel
formula will depend on the data available on column A ( ie starting
from A2 ) of new worksheet

Range("A1").Select
ActiveCell.FormulaR1C1 = "=VALUE(RC[2])&RC[6]"
Dim rng2 As Range
Set rng2 = Range(Cells(1, 2), Cells(1, 2).End(xlDown))
rng2.Offset(0, -1).Formula = Cells(1, 1).Formula

Thanks in advance, I’m a excel VBA beginner
Regards
Len

 

[OssieMac]

Hellow Len,

I am not really sure what it is you are trying to do. Firstly the formula in
A1. It comes out as
=VALUE(C1)&G1.
Are you simply concatenating or concatenating and findin ghe value of say
some numerics.
=C1 & G1 will simply concatenate.

=VALUE(C1&G1) will return the actual value of 2 concatenated cells.

Also note that if you leave off the R1C1 from formula you can enter the
formula like the following in VBA and it is easier to understand. (There is
an equals sign inside the quotes)

ActiveCell.Formula = "=(C1&G1)" 'To just concatenate

ActiveCell.Formula = "=VALUE(C1&G1)" 'To concatenate and find value

In setting your range you are using column 2 and then offsetting -1 for
column A. Are you testing for the last row in column A or B?

Anyway if you just want to copy the formula in column A and paste it down to
the last cell in column A with data then like this.

Range("A1").FormulaR1C1 = "=VALUE(RC[2])&RC[6]"

Dim rng2 As Range
Set rng2 = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

Cells(1, 1).Copy Destination:=rng2

 

[FSt1]

hi
you could add some qualifing code to test for the presents of data.

Dim r As Long
r = Cells(Rows.Count, "B").End(xlUp).Row
MsgBox "last used row is " & r 'for testing purposes only
If r < 2 Then 'qualifier
MsgBox "can't copy down. no data!!"
Exit Sub
Else
'your code here
end if

regards
FSt1

 

[Len]

Hellow Len,

I am not really sure what it is you are trying to do. Firstly the formulain
A1. It comes out as
=VALUE(C1)&G1.
Are you simply concatenating or concatenating and findin ghe value of say
some numerics.
=C1 & G1 will simply concatenate.

=VALUE(C1&G1) will return the actual value of 2 concatenated cells.

Also note that if you leave off the R1C1 from formula you can enter the
formula like the following in VBA and it is easier to understand. (There is
an equals sign inside the quotes)

ActiveCell.Formula = "=(C1&G1)"  'To just concatenate

ActiveCell.Formula = "=VALUE(C1&G1)"  'To concatenate and find value

In setting your range you are using column 2 and then offsetting -1 for
column A. Are you testing for the last row in column A or B?

Anyway if you just want to copy the formula in column A and paste it downto
the last cell in column A with data then like this.

Range("A1").FormulaR1C1 = "=VALUE(RC[2])&RC[6]"

Dim rng2 As Range
Set rng2 = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

Cells(1, 1).Copy Destination:=rng2

--
Regards,

OssieMac

Hi,

I have this problem when I run excel macro below on a new worksheet
where there is no data except  the header on row1, then the excel
formula still copying down the whole column A with 0 value and it will
stop copying until the last used cell if there is data on column A
How can I modify this excel macro to decide whether to copy the excel
formula will depend on the data available on column A ( ie starting
from A2 ) of new worksheet

Range("A1").Select
    ActiveCell.FormulaR1C1 = "=VALUE(RC[2])&RC[6]"
    Dim rng2 As Range
    Set rng2 = Range(Cells(1, 2), Cells(1, 2).End(xlDown))
rng2.Offset(0, -1).Formula = Cells(1, 1).Formula

Thanks in advance, I’m a excel VBA beginner
Regards
Len- Hide quoted text -

- Show quoted text -

Hi ,

Sorry, my statement was not clear and the excel formula ( ie from A1 )
need to copy down in column A depending on the last used cell of
column B

Thks & Regards
Len

 

[OssieMac]

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

 

[Len]

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

Hi ,

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

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

Thks & Regards
Len

 

[Len]

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