Canot not set cell Value via VBA

[mootz]

I cannot set the value of a cell in the same book but different sheet. I'm new to Excel/VBA but alot of typing with Visual Foxpro (and others). I have tried every possible combo and every read Kofler's book on Excel VBA. Here is my code:

Worksheets("Stat Sheet").Range("B9").Value = 12

I can run it in the immediate window but get a "VALUE#" error when I run it a single line VBA function. Same problem in Excel 97 and 2000.

TIA, John (P.S. I tried to search here but keep getting error with the SQL string).

[cornbread]

you need to say
ActiveWorkbook.Sheets(2).Range("b9") = 12


[ This Message was edited by: cornbread on 2002-05-22 12:24 ]

[mootz]

Thanks, I tried your code but still get the same error.

Any other ideas?
John

[DZarlengo]

Did you try:

ActiveWorkBook.WorkSheets("Stat Sheet").Range("B9").Value = 12

[cornbread]

you need to say
sub put1()
ActiveWorkBook.Sheets(2).Range("b2") = 12
end sub

or

Sub put2()
Sheet2.Range("b2") = 12
End Sub

whatever the name of the sheet within vba is is what you need to use not the name on the sheet tab.


[ This Message was edited by: cornbread on 2002-05-22 13:07 ]

[mootz]

Ok, does anybody have a hammer. I need to hit my head.

I can get the Sub to run via the Immediate window but not directly from Excel via a Function statement. The function only calls the Sub. If a comment out the Sub call the function works, otherwise I get the same "#VALUE" error. BTW, I ca also succesfully can the function from the immediate window. Any blows form the hammer for me??

John

[James]

Hi mootz,

Before you use a hammer on yourself, can yo post your code?

Regards,
James

[mootz]

Here is the basic code (2-300 lines in rest):

Public Function Temp() As Integer
Put1
Temp = 1
End Function


Sub Put1()
ActiveWorkbook.Sheets("Day 1").Range("c9") = 545
End Sub

The function calls the Sub because I don't know how to call a Sub directly from Excel (or how to spell the topic).

John

[James]

What about:

Range("C9").Value = 545

Regards,
James

[mootz]

I need to change the cell in a different worksheet, so I need the Worksheet(x) object.

I did try it (and everything else a week ago) but still same error if called from Excel (vs immediate window).

john