Can’t set range from two worksheets VBA

user_15263574 0
Viewed 34

I can’t understand why I keep getting the “Application-defined or object-defined error.” I have tried moving the code to a separate module and I’ve double checked worksheet names.

Sub CreateCalculations()
Set SummTrans = Worksheets(“Summary”).Range(“I3”, Range(“I3”).End(xlDown))
Set RegTrans = Worksheets(“Register”).Range(“A3”, Range(“A3”).End(xlDown))
End Sub

For some reason this code does work but it is not what I need.

Sub CreateCalculations()
Set SummTrans = Worksheets(“Summary”).Range(“I3”, Range(“I3”).End(xlDown))
Set RegTrans = Worksheets(“Register”).Range(“A3”)
End Sub
worksheet-function runtime-error range vba excel
1 Answers

Try,

Sub CreateCalculations()
    Dim SummTrans As Range
    Dim RegTrans As Range
    
    With Worksheets("Summary")
        Set SummTrans = .Range("I3", .Range("I3").End(xlDown))
    End With
    With Worksheets("Register")
        Set RegTrans = .Range("A3", .Range("A3").End(xlDown))
    End With
End Sub
user_81070920
Related