Run-time error '1004': Method 'Range' of object ' Worksheet' failed in VBA

Question

The code works fine when I run it manually, but when I call it in the calculate change event I get Run-time error '1004': Method 'Range' of object 'Worksheet failed. I simply want to copy the data that is specified in wksD4D to the specified cells in wksGenerator when Index_Count value changes (Index_Count is just a cell in wksGenerator with a formula).

The set of code is below:

Sub Initiate_Generator()

Dim wksGenerator As Worksheet
Dim wksD4D As Worksheet
Dim callCount As Range
Dim fullName As Range
Dim address As Range
Dim rngName As Range
Dim rngAddress As Range

Set wksGenerator = Worksheets("Generator")
Set fullName = wksGenerator.Range("A3")
Set address = wksGenerator.Range("B3")
Set callCount = wksGenerator.Range("Index_Count")

Set wksD4D = Worksheets("D4D")
Set rngName = wksD4D.Range("A" & callCount)
Set rngAddress = wksD4D.Range("B" & callCount)


'Fill values from D4D to Generator

fullName.Value = rngName
address.Value = rngAddress

End Sub

---

'This sheet1 also known as wksGenerator

Private Sub Worksheet_Calculate()
Static previousIndex
Static previousNum

If Range("Index_Count").Value <> previousIndex Then
    Call Initiate_Generator
    previousIndex = Range("Index_Count").Value
End If

' If Range("Dial_Num").Value <> previousNum Then
' Call Dialing_Number
' previousNum = Range("Dial_Num").Value
' End If

End Sub

Answer 1

re: code crashes

You are calling the code when calculation occurs.
The code you call changes a cell value which appears to cause calculation, so the code is called again...
Try this...
'---
Application.EnableEvents = False
If Range("Index_Count").Value <> previousIndex Then
Call Initiate_Generator
previousIndex = Range("Index_Count").Value
End If
Application.EnableEvents = True
'---

Note: every exit point in the code needs to set EnableEvents to True.
That means, at least, you need error handling to set EnableEvents to true.

NLtL
https://1drv.ms/u/s!Au8Lyt79SOuhZ_2VvKCLZxz9iwI?e=vnEabM
Add_Table of Contents, Calculate Payments, Custom_Functions, Professional_Compare

Answer 2

Try Application.Range(SomeName) instead of Range(SomeName) in case of named ranges.