CODEWITHSUNDEEP
excelvba
Nadz
Nadz

Reputation: 103

Runtime Error '1004'

I am trying to run this VBA code, but always get a Runtime Error '1004' Message

Dim clLookup As Range
Dim clDest As Range
Dim rws As Long

With wbk2.Sheets("SOA")
Set clDest = .Range("J2")
Set clLookup = .Range("G2")
End With

If clLookup.Offset(1, 0) <> vbNullString Then
rws = Range(clLookup, clLookup.End(xlDown)).Rows.Count
Set clDest = clDest.Resize(rws, 1)
End If

clDest.Formula = "=TODAY()-" & clLookup.Address(False, False) & ""
clDest.Value = clDest.Value ' Convert to value

Upvotes: 0

Views: 1399

Answers (1)

CynicalSection
CynicalSection

Reputation: 704

The problem is at this line: rws = Range(clLookup, clLookup.End(xlDown)).Rows.Count. Range object belongs to the execution sheet and not to the output sheet, SOA. This will work by changing the code to: 

rws = wbk2.Sheets("SOA").Range(clLookup, clLookup.End(xlDown)).Rows.Count

Upvotes: 2

Related Questions