CODEWITHSUNDEEP
excelvba
VBANovice
VBANovice

Reputation: 33

Error 1004 for range based cell copy

I've looked through countless examples (and there are many to be found) of how to use Ranges to accomplish a VBA user defined function/sub to copy the value of one specified cell to another. Unfortunately, no matter which I try, I am unable to avoid Error 1004 Application-defined or object-defined error. Here is the very simple test code:

Private Sub Foobar()
On Error GoTo fooErrorHandler
Dim c1 As Range
Dim c2 As Range
Set c1 = Sheets("Sheet1").Range("D2")
Set c2 = Sheets("Sheet1").Range("B3")
c2.Value = c1.Value
Exit Sub
fooErrorHandler:
MsgBox "Error Number: " & Err.Number & vbNewLine _
                              & "Description:    " & Err.Description
End Sub

Thanks for any help/pointers!

Upvotes: 3

Views: 224

Answers (2)

BrakNicku
BrakNicku

Reputation: 5991

Modifying cells inside UDF (a function, that is used inside a cell the same way as built-in formulas) is not allowed. If you remove a call to barfoo from C1 cell, both Foobar and barfoo should work without problems.

Upvotes: 0

David Zemens
David Zemens

Reputation: 53623

Generally speaking you cannot use a Function to manipulate the worksheet object. It looks like you're trying to get around that by invoking a subroutine from the barfoo function call. I suspect that is the error... If you run the subroutine foobar manually (press F5 or run from the macro menu) it should execute without error.

I confirm that this implementation raises the error, and also test the manual call to foobar without error.

If you can better describe the ultimate goal, perhaps we can recommend a more reliable way to achieve it.

Upvotes: 1

Related Questions