CODEWITHSUNDEEP
excelvbaruntime-error
Sofia Coelho
Sofia Coelho

Reputation: 1

Copy the value of C15 to D15 everytime the value in C15 changes: Run-time error '-2147417848 (80010108)': Method 'Range' of object ' Worksheet' failed

I want to copy the value of C15 to D15 everytime the value in C15 changes. Cell C15 has a simple 'if function' based on another cell within the same sheet. Any time I try to use my code, it initially works (the value is copied to cell D15). However, a second later, I get this error:

VBA Run-time error '-2147417848 (80010108)': Method 'Range' of object ' Worksheet' failed.

This is my code:

Private Sub Worksheet_Calculate()
    Dim Xrg As Range
    Set Xrg = Sheets("Calculator").Range("C15")
    If Not Intersect(Xrg, Sheets("Calculator").Range("C15")) Is Nothing Then
        Range("C15").Copy
        Range("D15").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End If
End Sub

If I debug it tells me the error is here:

Set Xrg = Sheets("Calculator").Range("C15")

Note: 'Calculator' is the correct name of the worksheet

Upvotes: 0

Views: 148

Answers (1)

user3598756
user3598756

Reputation: 29421

I cannot reproduce your error I could guess it's related to that "IF" formula residing in C15

But let me give you some pieces of advice

  1. unnecessary Set and If Not... check

setting Xrg and then checking that very same range against not being Nothing is useless since it will never be Nothing

hence you could simply go

Private Sub Worksheet_Calculate()
    Range("C15").Copy
    Range("D15").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
  1. clear clipboard

using PasteSpecial method of Range object after its Copy one, will leave the clipboard occupied with the result of this latter (as you can see with the copied cell remaining highlighted) and this could possibly affect the following actions

it's then good habit to release the clipboard by using :

Application.CutCopyMode = False

hence:

Private Sub Worksheet_Calculate()
    Range("C15").Copy
    Range("D15").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

  1. Since you're interested in copy/pasting values only, then use Value property of Range object

    Private Sub Worksheet_Calculate()
   Range("D15").Value = Range("C15").Value
End Sub

EDIT

elaborating a little bit more on:

I want to copy the value of C15 to D15 everytime the value in C15 changes.

my understanding is you cannot use Worksheet_Change() event since it wouldn't be fired by C15 changing value as a calculation result

in order to have C15 value copied to D15 only when C15 changes its value, you could use a Static variable that holds its value across subsequent macro runs:

Option Explicit    

Private Sub Worksheet_Calculate()    
    Static lastC15Value As Variant 
    
    With Range("C15")
        If lastC15Value <> .Value2 Then
            Range("D15").Value = .Value
            lastC15Value = .Value2
        End If
    End With
End Sub

this can be useful if your sheet calculations could also be triggered by cells that wouldn't affect C15 and provided calculation should be time consuming

while if your sheet calculations always affect cell C15 value, then you can stick to the previous codes

Upvotes: 0

Related Questions