Changing Cells, How to

[Mac Lingo]

How do I change a cell value from my VM Macro?

I think this should do it
CELLS(4,4) = "NEW VALUE"
but it doesn't seem to.

I've also tried
ACTIVECELL.CELLS(4,4) = "NEW VALUE"

What am I missing? I'm sure it's obvious, but I can't figure it out.

Thanks,
Mac

 

[Nigel]

Cells(4,4) = "New Value"
will work, unless you have some form of protection on.

It would be more precise to use

Cells(4,4).Value = "New Value"
or
Cells(4,4).Text = "New Value"

Activecell.cells(4,4) = "New Value"
should also work placing the value into the cell offset 4 rows and 4 columns
from the activecell

Cheers
Nigel

 

[Bob Phillips]

IS your target worksheet active? Try being explicit, that is

Worksheets("Sheet1").Cells(4,4).Value = "NEW VALUE"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mac Lingo]

Bob,
This is the line of code that does the write
Worksheets("Sheet3").Cells(4, 4).Value = "String"

When I execute this line, the values in the "Quick Watch" area goes "out of
context".
This code is called from the ActiveWorkSheet and I need to update another
cell on the same line.
I have figured out how to get the line number, but still can't do the write.

Thanks again for your help.

Capt Mac
Ocean Rider (Pacific right now, but am looking forward to the British Isles
one of these days)

 

[Bob Phillips]

Not sure on this one. Is the sheet or the cell protected?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mac Lingo]

Bob,
I don't know how to protect anything; haven't gotten that far in haveing to
VB Program.

SO I think the answer to that is NO, but how would I know.

Also, I got a Bad Address bounce back on your email address. Any idea why.
Maybe our Patriot Act won't let emails out of the US any more.

Capt Mac

 

[Mac Lingo]

Missing Information from the Problem - The VB Macro is a FUNCTION being
called from the spreadsheet.

The Solution: - A Rule: You CAN'T change the enviornment a Function is being
called from. I suppose this is because it too easily can end up in an
infinite loop, but for whatever reason EXCEL won't allow it.

Answer: You have to write a SUBROUTINE instead.

 

[Bob Phillips]

Marc,

Not correct. Try this

Function ChangeIt(rng As Range, val)
rng.Value = val
End Function

and in the worksheet, use

=ChangeIt(D4,"NEW VALUE")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)