ExcelBanter - Column ROW CEL range vba

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Column ROW CEL range vba (https://www.excelbanter.com/excel-programming/345023-column-row-cel-range-vba.html)

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

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

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

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

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

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

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