Copy cell value via VBA if specific cell is changed

[Schturman]

Hi to all
I need some help to change the code that I use to copy value...
This code I use:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

 If Not Intersect(Target, Range("B2")) Is Nothing Then
  Set rng = Range("C2")
  rng.Value = Range("A2")
    Else
    If Not Intersect(Target, Range("B3")) Is Nothing Then
     Set rng = Range("C3")
     rng.Value = Range("A2")
     End If
  End If

End Sub

If I do any changes to B2, it will copy A2 to C2
If I do any changes to B3, it will copy A2 to C3
And so on...

When I add new row (for example insert value to B4), I need to update my code that will do the same copy to C4
Can someone help me to change the code that will do what I want without every time updating VBA code ?
Thanks

 

[Schturman]

Target.Address is the cell address of the cell in column B that you edit. If you offset(0,1) you are referencing a cell in the same row, one cell to the right of B, which is empty. Do you see the solution?

Multiply by the Target.Value, not the offset.
Target.Offset(0, 1) = Range("A2") * Target.Value

It's really cool, Thanks !
I get the same result by changed this:

If Not Intersect(Target, Range("B:B")) Is Nothing Then Target.Offset(0, 1) = Range("A2") * Target.Offset(0, 0)

But now I know, instead Offset I can use Target.Value

Thank you very much !

 

[Joe4]

It's really cool, Thanks !
I get the same result by changed this:

If Not Intersect(Target, Range("B:B")) Is Nothing Then Target.Offset(0, 1) = Range("A2") * Target.Offset(0, 0)

But now I know, instead Offset I can use Target.Value

Thank you very much !

Well, not quite. You have mixed up a few things (concepts).

"Target" is just a range object/variable.

"Target.Value" returns the value of that range.
However, adding the ".Value" is really not necessary.
If you leave it off, like "Target", it will default to the value.

Adding ".Offset(0,0)" is just saying from your starting range, move 0 rows down and 0 row to the right (in other words, stay where you are!).

So, the shortest form to write it everything after your "Then" would be:

Target.Offset(0, 1) = Range("A2") * Target

The fully qualified equivalent version would be:

Target.Offset(0, 1).Value = Range("A2").Value * Target.Value

Adding "Offset(0,0)" serves no purpose whatsoever.

I hope that makes sense!

 

[Schturman]

Well, not quite. You have mixed up a few things (concepts).

"Target" is just a range object/variable.

"Target.Value" returns the value of that range.
However, adding the ".Value" is really not necessary.
If you leave it off, like "Target", it will default to the value.

Adding ".Offset(0,0)" is just saying from your starting range, move 0 rows down and 0 row to the right (in other words, stay where you are!).

So, the shortest form to write it everything after your "Then" would be:

Target.Offset(0, 1) = Range("A2") * Target

The fully qualified equivalent version would be:

Target.Offset(0, 1).Value = Range("A2").Value * Target.Value

Adding "Offset(0,0)" serves no purpose whatsoever.

I hope that makes sense!

WoW, thank you for explanation !!! I learned some cool stuff !

 

[Joe4]

You are most welcome!

 

[Schturman]

Hi.
I want to ask another question about this code.
For example if I use Text in the column B, I get error of code and this OK, because it can't calculate:

Range("A2") * Target

I changed code a little bit to:

Private Sub Worksheet_Change(ByVal Target As Range)
'Avoid error if user selects & deletes multiple rows
If Target.Columns.Count > 1 Then Exit Sub

 If Not Intersect(Target, Range("B:B")) Is Nothing Then
   If Target = Target.Text Then
   Application.EnableEvents = False
   Target.Offset(0, 1) = ""
   Else
   Application.EnableEvents = False
   Target.Offset(0, 1) = Range("A2") * Target
   End If
   
 End If
 Application.EnableEvents = True 'error handle

End Sub

Now it working good.

And a questions:
1. Is there a more elegant way to do this or this one is ok?

2. I noticed about something that didn't saw before because didn't tried this... When I drag value (text or number) in a column B more than one cell - I get error of code. I can copy value one by one without errors, but dragging cause error. Can it be fixed somehow ?
Thanks

 

[Micron]

maybe try this instead
If Target.Columns.Count > 1 OR Target.Rows.Count > 1 then Exit Sub

Not sure what you mean by "if I use Text in the column B, I get error of code".
Maybe test if the cell contains a number and if not don't calculate?
Don't know if there's a better way or not.

 

[Joe4]

If Target.Columns.Count > 1 OR Target.Rows.Count > 1 then Exit Sub

If you change it to this:

If Target.CountLarge > 1 Then Exit Sub

it will exit the sub whenever multiple cells are changed at once (regardless if there are columns or rows).
This is handy to handle things like large deletions or inserts of rows & columns.

 

[Schturman]

Thanks to both of you!
Ok, I tested both of yours variants, both of them working perfect.
But both of them not calculate if I drag numbers in column B. I understand because it do

Exit Sub

When I drag numbers one by one, it calculate perfectly.
It possible to do calculating when I drag more than one cell ? For example I write 20 euro in cell B4 and drag it till B8, I expect to get calculated price in C5,C6,C7,C8 (If I dragging one by one I get this...)

Not sure what you mean by "if I use Text in the column B, I get error of code".
Maybe test if the cell contains a number and if not don't calculate?

About text... Some times I can use text in column B instead the numbers. For example If I don't need to calculate, I can write in column B something like: "Stable price in NIS" and in column C, will write the price manually. That mean if I use Text' it should not calculate, this is a reason why I changed a part of the code to this and asked about more elegant way:

   If Target = Target.Text Then
   Application.EnableEvents = False
   Target.Offset(0, 1) = ""
   Else
   Application.EnableEvents = False
   Target.Offset(0, 1) = Range("A2") * Target
   End If

In the picture you can see I dragged the 21 euro down and it not calculate because of

Exit Sub

If it not possible to change dragging behavior, it's also ok. Like I said before I noticed about error code by mistake when dragged a few cells Now error fixed and it's ok.

 

[Joe4]

What is the point of this line here?

If Target = Target.Text Then

What are you trying to accomplish with that?

 

[Micron]

@Joe4, thanks for continuing to educate. I didn't know about that property.
Not being an Excel expert I'm going to say that you're going to have to loop over the selected cells if you want to drag values down, then validate that each cell value is a number and if not, don't multiply.