Error 1004 when cell set to a value

[Bob A]

I get an error 1004 when I execute the VBA function below. I've tried
everyting, Range = ID, cells(1) = ID, Range("test").Cells(1).Value = ID, you
name it. Always the same error.

Range "Test' is a named range of one cell on sheet 1 that contains the value
997.

The Function below reads the correct value out of the named range into the
var ID, but errors out when I write to the cell. Cell is not locked. Sheet
is not protected. Trust Center allows VBA code. Code is in a seperate
module, not on the sheet code.

Purpose is to increment a value, and save it in a cell on the sheet.

I call the function from another cell, like this: =IF(B4="","",NewID())

I've been at this for hours, researching what could be wrong. About ready to
toss the computer out the window. Anyone have any ideas?

Function NewID()
Dim ID As Long

ID = Range("Test").Cells(1).Value
ID = ID + 1
Range("Test").Cells(1).Value = ID 'This line throws the error

NewID = ID

End Function

 

[FloMM2]

Bob A,
This is the solution I came up with:

Sub Get_NewID()
Dim ID As Long, NewID As Long

ID = Worksheets("Sheet1").Range("A1").Value
NewID = ID + 1


Range("A1").Select
Selection.Value = NewID


End Sub
"Sheet1" you should change to the name of your sheet. What this doesis this:
If 1 is in cell A1, this program reads the "1" and adds 1 to it.
Is that what you want?
hth

 

[Dave Peterson]

User defined Functions return values to cells that hold the formula--they can't
touch the values in other cells.

Maybe it's time to look at worksheet events that can do what you want???

 

[Patrick Molloy]

its simply
Range("test") = ID

yoo don't need the .Value as it is the default method, but its better
codeing as its easier to unserstand, so

Range("test").Value = ID

you could also have used

Range("test").Value = Range("test").Value +1

 

[Dave Peterson]

Ps. I should have said that UDF that are used in cells in worksheets can only
return values to the cells that hold that formula.

Functions used in your code (called by other functions/subs) can do what you
want.

But no you can't have a UDF in a cell call another function that tries to break
this. Excel is pretty smart.

 

[Patrick Molloy]

change FUNCTION to SUB