#Value error when setting a Cell value
#Value error when setting a Cell value
(OP)
I'm a newbie to Visual Basic on Excel. I wanted to do a very simple task, and have wasted a couple hours trying. I'm hoping somebody can help. Here is a very simple function, but I get #Value error when I use it.
Function Test()
' The following doesn't work - I get a # Value error: WHY
[A2].Value = 34
' The following does work:
Test = [B2].Value
End Function
Function Test()
' The following doesn't work - I get a # Value error: WHY
[A2].Value = 34
' The following does work:
Test = [B2].Value
End Function





RE: #Value error when setting a Cell value
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: #Value error when setting a Cell value
Maybe I should provide some more details in case I'm missing something very fundamental. In Excel I selected Tools->Macro->Visual Basic Editor. I then picked 'Insert->Module' and put the code in the module.
Thanks again for the feedback.
RE: #Value error when setting a Cell value
Referring to Cells by Using Shortcut Notation
You can use either the A1 reference style or a named range within brackets as a shortcut for the Range property. You don't have to type the word "Range" or use quotation marks, as shown in the following examples.
Sub ClearRange()
Worksheets("Sheet1").[A1:B5].ClearContents
End Sub
Sub SetValue()
[MyRange].Value = 30
End Sub
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: #Value error when setting a Cell value
This is disappointing, but I need to move on to plan B so I can finish my task. I will continue to investigate this in the background, because I think this kind of VB programming in Excel will be useful to me, if I can get it to work.
RE: #Value error when setting a Cell value
Some things are not making sense. Your above code is a function, not a subroutine. How are you calling the function? If you are trying to set a cell value with a user defined function, then you would use
Function Test()
Test = [B2].Value
End Function
then in your spreadsheet, you would set the formula for cell A2 to =Test which in turn makes its value equal to the value of B2
If my memory serves me correct, you can't change the value of other cells with a user defined function
RE: #Value error when setting a Cell value
Thanks for the feedback. What you described works, as I mentioned in my original post. However, you mention "you can't change the value of other cells with a user defined function". That is exactly what I want to do. IRstuff said it worked on his computer.
I ran the function in the VB debugger, and it worked. However, when I use it in the spreadsheet it doesn't work. Maybe the problem is, as you say, "how I'm calling the function". I am just putting '=Test()' in a cell. Is there another way to call the function? As I said, I'm a newbie at doing VB in Excel, and I'm probably doing something dumb.
Thanks again for your help.
RE: #Value error when setting a Cell value
Thanks again
RE: #Value error when setting a Cell value
Thanks for your help CFCEng and IRstuff.
RE: #Value error when setting a Cell value
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.