Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column ROW CEL range vba
Hello,
I need a macro that copy a value out of for example $C8 and copy it as a value to $D8 for example. So macro must also work on for example ROW C25 and D25. When i record the macro it only works with Row 8. Can anyone help? Thanks Pieter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column ROW CEL range vba
try
range("d8").value=range("c8") -- Don Guillett SalesAid Software "Pieter" wrote in message ... Hello, I need a macro that copy a value out of for example $C8 and copy it as a value to $D8 for example. So macro must also work on for example ROW C25 and D25. When i record the macro it only works with Row 8. Can anyone help? Thanks Pieter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column ROW CEL range vba
Thanks Don but that do not work when i am in row 30 for example
i want a macro to copy the value of column for example 8 and copy it as a value to column Column 5 of the same row.... "Don Guillett" wrote: try range("d8").value=range("c8") -- Don Guillett SalesAid Software "Pieter" wrote in message ... Hello, I need a macro that copy a value out of for example $C8 and copy it as a value to $D8 for example. So macro must also work on for example ROW C25 and D25. When i record the macro it only works with Row 8. Can anyone help? Thanks Pieter |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column ROW CEL range vba
If you want this to happend when you manually edit a cell in column C then
Right click on the sheet tab and select view code. Put in code like this: Private Sub Worksheet_Change(ByVal Target As Range) if Target.count 1 then exit sub if Target.column = 3 then Target.offset(0,1).value = Target.Value end if End Sub If cells in C are calculated formulas Private Sub Worksheet_Calculate() On Error goto ErrHandler Application.EnableEvents = False set rng = range(cells(1,3),cells(rows.count,3).End(xlup)) rng.copy Range("D1").PasteSpecial xlPasteValues ErrHandler: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Pieter" wrote in message ... Thanks Don but that do not work when i am in row 30 for example i want a macro to copy the value of column for example 8 and copy it as a value to column Column 5 of the same row.... "Don Guillett" wrote: try range("d8").value=range("c8") -- Don Guillett SalesAid Software "Pieter" wrote in message ... Hello, I need a macro that copy a value out of for example $C8 and copy it as a value to $D8 for example. So macro must also work on for example ROW C25 and D25. When i record the macro it only works with Row 8. Can anyone help? Thanks Pieter |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column ROW CEL range vba
Still not what i want i explain it by an example
in cell b2 = 25000 in cell c2 = 20000 in cell d2 = +b2-c2 now i want a macro that copy the value (not the formul) of cell d2 to b2 and change the value of c2 in 0 "Tom Ogilvy" wrote: If you want this to happend when you manually edit a cell in column C then Right click on the sheet tab and select view code. Put in code like this: Private Sub Worksheet_Change(ByVal Target As Range) if Target.count 1 then exit sub if Target.column = 3 then Target.offset(0,1).value = Target.Value end if End Sub If cells in C are calculated formulas Private Sub Worksheet_Calculate() On Error goto ErrHandler Application.EnableEvents = False set rng = range(cells(1,3),cells(rows.count,3).End(xlup)) rng.copy Range("D1").PasteSpecial xlPasteValues ErrHandler: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Pieter" wrote in message ... Thanks Don but that do not work when i am in row 30 for example i want a macro to copy the value of column for example 8 and copy it as a value to column Column 5 of the same row.... "Don Guillett" wrote: try range("d8").value=range("c8") -- Don Guillett SalesAid Software "Pieter" wrote in message ... Hello, I need a macro that copy a value out of for example $C8 and copy it as a value to $D8 for example. So macro must also work on for example ROW C25 and D25. When i record the macro it only works with Row 8. Can anyone help? Thanks Pieter |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column ROW CEL range vba
and it must work in all the rows below
"Pieter" wrote: Still not what i want i explain it by an example in cell b2 = 25000 in cell c2 = 20000 in cell d2 = +b2-c2 now i want a macro that copy the value (not the formul) of cell d2 to b2 and change the value of c2 in 0 "Tom Ogilvy" wrote: If you want this to happend when you manually edit a cell in column C then Right click on the sheet tab and select view code. Put in code like this: Private Sub Worksheet_Change(ByVal Target As Range) if Target.count 1 then exit sub if Target.column = 3 then Target.offset(0,1).value = Target.Value end if End Sub If cells in C are calculated formulas Private Sub Worksheet_Calculate() On Error goto ErrHandler Application.EnableEvents = False set rng = range(cells(1,3),cells(rows.count,3).End(xlup)) rng.copy Range("D1").PasteSpecial xlPasteValues ErrHandler: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Pieter" wrote in message ... Thanks Don but that do not work when i am in row 30 for example i want a macro to copy the value of column for example 8 and copy it as a value to column Column 5 of the same row.... "Don Guillett" wrote: try range("d8").value=range("c8") -- Don Guillett SalesAid Software "Pieter" wrote in message ... Hello, I need a macro that copy a value out of for example $C8 and copy it as a value to $D8 for example. So macro must also work on for example ROW C25 and D25. When i record the macro it only works with Row 8. Can anyone help? Thanks Pieter |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column ROW CEL range vba
Why not just skip the formula in D2 and after you enter your values,
select column c do edit =copy then select column b do edit=Paste special, select values and subtract you can then select column C and do edit=goto=Special, select constants and numbers, then in the formula bar, enter 0 and hit Ctrl+enter Turn on the macro recorder while you do it manually if you need code. -- Regards, Tom Ogilvy "Pieter" wrote in message ... and it must work in all the rows below "Pieter" wrote: Still not what i want i explain it by an example in cell b2 = 25000 in cell c2 = 20000 in cell d2 = +b2-c2 now i want a macro that copy the value (not the formul) of cell d2 to b2 and change the value of c2 in 0 "Tom Ogilvy" wrote: If you want this to happend when you manually edit a cell in column C then Right click on the sheet tab and select view code. Put in code like this: Private Sub Worksheet_Change(ByVal Target As Range) if Target.count 1 then exit sub if Target.column = 3 then Target.offset(0,1).value = Target.Value end if End Sub If cells in C are calculated formulas Private Sub Worksheet_Calculate() On Error goto ErrHandler Application.EnableEvents = False set rng = range(cells(1,3),cells(rows.count,3).End(xlup)) rng.copy Range("D1").PasteSpecial xlPasteValues ErrHandler: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Pieter" wrote in message ... Thanks Don but that do not work when i am in row 30 for example i want a macro to copy the value of column for example 8 and copy it as a value to column Column 5 of the same row.... "Don Guillett" wrote: try range("d8").value=range("c8") -- Don Guillett SalesAid Software "Pieter" wrote in message ... Hello, I need a macro that copy a value out of for example $C8 and copy it as a value to $D8 for example. So macro must also work on for example ROW C25 and D25. When i record the macro it only works with Row 8. Can anyone help? Thanks Pieter |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column ROW CEL range vba
Tom,
thanks for your response, but now it works with the whole column. i want that it works on only an active row. so cell for example z 120 is selected then i want that it copy the value of d120 (not the formule) to B120 and change C120 in 0. gr. "Tom Ogilvy" schreef: Why not just skip the formula in D2 and after you enter your values, select column c do edit =copy then select column b do edit=Paste special, select values and subtract you can then select column C and do edit=goto=Special, select constants and numbers, then in the formula bar, enter 0 and hit Ctrl+enter Turn on the macro recorder while you do it manually if you need code. -- Regards, Tom Ogilvy "Pieter" wrote in message ... and it must work in all the rows below "Pieter" wrote: Still not what i want i explain it by an example in cell b2 = 25000 in cell c2 = 20000 in cell d2 = +b2-c2 now i want a macro that copy the value (not the formul) of cell d2 to b2 and change the value of c2 in 0 "Tom Ogilvy" wrote: If you want this to happend when you manually edit a cell in column C then Right click on the sheet tab and select view code. Put in code like this: Private Sub Worksheet_Change(ByVal Target As Range) if Target.count 1 then exit sub if Target.column = 3 then Target.offset(0,1).value = Target.Value end if End Sub If cells in C are calculated formulas Private Sub Worksheet_Calculate() On Error goto ErrHandler Application.EnableEvents = False set rng = range(cells(1,3),cells(rows.count,3).End(xlup)) rng.copy Range("D1").PasteSpecial xlPasteValues ErrHandler: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Pieter" wrote in message ... Thanks Don but that do not work when i am in row 30 for example i want a macro to copy the value of column for example 8 and copy it as a value to column Column 5 of the same row.... "Don Guillett" wrote: try range("d8").value=range("c8") -- Don Guillett SalesAid Software "Pieter" wrote in message ... Hello, I need a macro that copy a value out of for example $C8 and copy it as a value to $D8 for example. So macro must also work on for example ROW C25 and D25. When i record the macro it only works with Row 8. Can anyone help? Thanks Pieter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AVERAGE a range in a column if another column's range equals a val | Excel Discussion (Misc queries) | |||
formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
CountIf first column range = "Word" and second column range <> 0 | Excel Worksheet Functions | |||
Transfering VBA Array Column Range to Excel Column Range | Excel Programming |