Run Time Error Calling Value from one cell and placing in another

DeezNuts

Board Regular
Joined
Aug 12, 2014
Messages
177
I am having a small problem and not sure how to resolve it. I have a formula in E5 which is
Code:
=A5/A3*C3
and I have a vba that copies that value and places it in C5
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Range("C5").Value = ActiveSheet.Range("E5").Value
End Sub
When I trigger E5 and its number changes the number in C5 does change correctly but I get a run time error
0uAgnXK.png


If I click Debug the this part of the VBA code is highlighted yellow
Code:
ActiveSheet.Range("C5").Value = ActiveSheet.Range("E5").Value

Anyone know what would cause this to happen?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Use like this
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Range("C5").Value = Range("E5").Value
End Sub
 
Upvote 0
That gives this error
ezuWYjK.png



Excel 2013
ABCDE
1Aspect Ratio Calculator
2H1=H2
3326309
4W1W2
5580550550

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
E5=A5/A3*C3

<tbody>
</tbody>

<tbody>
</tbody>

No other macros or scripts and only that one formula on the page.







******************************
EDIT

Got it to work right
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
    Range("C5").Value = Range("E5").Value
  Application.EnableEvents = True
End Sub

Had to end the loop thank you Momentman
 
Last edited:
Upvote 0
Anyone know what would cause this to happen?
The problem is that the Worksheet_Change event code is triggered when a change is made on the worksheet (naturally :)). However, your code is making a change on the worksheet, which triggers the ws_Change code which makes a change to the worksheet which triggers the ws_Change code which ...

You need to disable/re-enable events
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Range("C5").Value = Range("E5").Value
    Application.EnableEvents = True
End Sub
 
Upvote 0
Thank you Peter_SSs I caught that and was editing the post when you posted :) Thank you both I appreciate it.
 
Upvote 0

Forum statistics

Threads
1,196,288
Messages
6,014,495
Members
441,823
Latest member
GregG26

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top