VBA How do I set a value in a cell?

Hi All Excel experts!

I'm using Excel 2003 Professional Edition.

How do I set a value in a cell from a VBA function. I can't return the value
from the function, it must be entered from VBA code.

I have tried this line:
Range("Sheet1:M5").Value = "hello"
and:
Range("Sheet1!M5").Value = "hello"


No luck, I only get "#VALUE!" in the cell where the VBA script is running
from, and no "hello" in cell M5.


Regards,
Lars-Inge

Sheet("Sheet1").Range("M5").Value = "hello"

--
Cheers
Nigel



"Lars-Inge Tønnessen (VJ# MVP)" <http://emailme.larsinge.com> wrote in
message news:[email protected]...
> Hi All Excel experts!
>
> I'm using Excel 2003 Professional Edition.
>
> How do I set a value in a cell from a VBA function. I can't return the
value
> from the function, it must be entered from VBA code.
>
> I have tried this line:
> Range("Sheet1:M5").Value = "hello"
> and:
> Range("Sheet1!M5").Value = "hello"
>
>
> No luck, I only get "#VALUE!" in the cell where the VBA script is running
> from, and no "hello" in cell M5.
>
>
> Regards,
> Lars-Inge
>
>

Lars-Inge,
Try
Worksheets("Sheet1").range("M5").value = "hello"
grtx's Martijn

"Lars-Inge Tønnessen (VJ# MVP)" <http://emailme.larsinge.com> schreef in
bericht news:[email protected]...
> Hi All Excel experts!
>
> I'm using Excel 2003 Professional Edition.
>
> How do I set a value in a cell from a VBA function. I can't return the
value
> from the function, it must be entered from VBA code.
>
> I have tried this line:
> Range("Sheet1:M5").Value = "hello"
> and:
> Range("Sheet1!M5").Value = "hello"
>
>
> No luck, I only get "#VALUE!" in the cell where the VBA script is running
> from, and no "hello" in cell M5.
>
>
> Regards,
> Lars-Inge
>
>

Hi

When you really are writing a function, then I'm afrayd you'll encounter
problems anyway. As rule you can't change anything in Excel function - btw
you can't change any cell values. Write a procedure instead, or be content
with function displaying a result in cell it resides.


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )



"Lars-Inge Tønnessen (VJ# MVP)" <http://emailme.larsinge.com> wrote in
message news:[email protected]...
> Hi All Excel experts!
>
> I'm using Excel 2003 Professional Edition.
>
> How do I set a value in a cell from a VBA function. I can't return the
> value from the function, it must be entered from VBA code.
>
> I have tried this line:
> Range("Sheet1:M5").Value = "hello"
> and:
> Range("Sheet1!M5").Value = "hello"
>
>
> No luck, I only get "#VALUE!" in the cell where the VBA script is running
> from, and no "hello" in cell M5.
>
>
> Regards,
> Lars-Inge
>
>

This gave me "#VALUE!":

Formula in cell M16 "=go()"

Public Function go() As String
Worksheets("Sheet1").Range("M5").Value = "hello"
End Function


Regards,
Lars-Inge

This gave me "Compile error: sub or Function not defined".

Public Function go() As String
Sheet("Sheet1").Range("M5").Value = "hello"
End Function



I have also tried:

Public Function go() As String
Sheets("Sheet1").Range("M5").Value = "hello"
End Function

With the result: "#VALUE!" in the formula cell M16.





I can read cells with this code, so I think the VBA "engine" is running and
working. I would like to write a value in a cell (, not read it)

Public Function go() As String
MsgBox ("->" & Range("B1").Value)
End Function



Regards,
Lars-Inge

Yeaaahhh!!! :o)

This works like a dream.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
gogogo
End Sub

Public Sub gogogo()
Range("M5").Value = "Hello"
End Sub


Regards,
Lars-Inge

Change:
Public Function go() As String
Worksheets("Sheet1").Range("M5").Value = "hello"
End Function

To:
Public Sub go() As String
Worksheets("Sheet1").Range("M5").Value = "hello"
End Sub

HTH

"Lars-Inge Tønnessen (VJ# MVP)" <http://emailme.larsinge.com> wrote in
message news:[email protected]...
> This gave me "#VALUE!":
>
> Formula in cell M16 "=go()"
>
> Public Function go() As String
> Worksheets("Sheet1").Range("M5").Value = "hello"
> End Function
>
>
> Regards,
> Lars-Inge
>
>

Thanks!

Cheers,
Lars-Inge