Excel VBA write to cell 1

[john30120]

Using Excel 2003.. I am trying to write from within a VBA function back to the worksheet.

j=Range("B1").Value reads the value into j just fine

but

Range("B1").Value = j generates an error

the worksheet is not protected and the function result reads back into its cell OK.

I am missing something simple but what?

 

[Scott24x7]

Try:

j=Range("B1).Value
...
(manipulate your value)
...
Range("B1:B1").Select
Selection.PasteSpecial Paste:=j, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Hi,

I am trying to write from within a VBA function back to the worksheet.

A spreadsheet function cannot change any other cell in the workbook other than the cell the function is in.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, please post VBA macro questions in forum707 to improve your chances of getting relevent information.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[john30120]

Wow! That seems an unnecessary limitation. I have seen examples of code elsewhere that seems to write to cells butgivien my struggles it seems you are correct.

Thanks

 

[GlennUK]

If you want code that writes to cells then it has to be via a sub routine rather than a function.

Cheers, Glenn.

Beauty is in the eye of the beerholder.