Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy formula
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 -- Regards, OssieMac "Len" wrote: 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, Im a excel VBA beginner Regards Len |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy formula
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" wrote: 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, Im a excel VBA beginner Regards Len |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy formula
On May 14, 11:23*am, OssieMac
wrote: 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 -- Regards, OssieMac "Len" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy formula
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy formula
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy formula result (text) only - without copying formula | Excel Discussion (Misc queries) | |||
I copy a formula and the results copy from the original cell | Excel Discussion (Misc queries) | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
Copy formula so destination displays formula as text | Excel Discussion (Misc queries) |