[vba] Writing a string to a cell in excel

I am trying to write a value to the "A1" cell, but am getting the following error:

Application-defined or object-defined error '1004'

I have tried many solutions on the net, but none are working. I am using excel 2007 and the file extensiton is .xlsm.

My code is as follows:

Sub varchanger()
On Error GoTo Whoa
Dim TxtRng  As Range

Worksheets("Game").Activate
ActiveSheet.Unprotect

Set TxtRng = ActiveWorkbook.Sheets("Game").Cells(1, 1)
TxtRng.Value = "SubTotal"

'Worksheets("Game").Range("A1") = "Asdf"

LetsContinue:
    Exit Sub
Whoa:
    MsgBox Err.number
    Resume LetsContinue
End Sub

Edit: After I get error if I click the caution icon and then select show calculation steps its working properly

replace Range("A1") = "Asdf" with Range("A1").value = "Asdf"

I've had a few cranberry-vodkas tonight so I might be missing something...Is setting the range necessary? Why not use:

Activeworkbook.Sheets("Game").Range("A1").value = "Subtotal"

Does this fail as well?

Looks like you tried something similar:

'Worksheets("Game").Range("A1") = "Asdf"

However, Worksheets is a collection, so you can't reference "Game". I think you need to use the Sheets object instead.

try this instead

Set TxtRng = ActiveWorkbook.Sheets("Game").Range("A1")

ADDITION

Maybe the file is corrupt - this has happened to me several times before and the only solution is to copy everything out into a new file.

Please can you try the following:

• Save a new xlsm file and call it "MyFullyQualified.xlsm"
• Add a sheet with no protection and call it "mySheet"
• Add a module to the workbook and add the following procedure

Does this run?

 Sub varchanger()

 With Excel.Application
    .ScreenUpdating = True
    .Calculation = Excel.xlCalculationAutomatic
    .EnableEvents = True
 End With

 On Error GoTo Whoa:

    Dim myBook As Excel.Workbook
    Dim mySheet As Excel.Worksheet
    Dim Rng  As Excel.Range

    Set myBook = Excel.Workbooks("MyFullyQualified.xlsm")
    Set mySheet = myBook.Worksheets("mySheet")
    Set Rng = mySheet.Range("A1")

    'ActiveSheet.Unprotect


    Rng.Value = "SubTotal"

    Excel.Workbooks("MyFullyQualified.xlsm").Worksheets("mySheet").Range("A1").Value = "Asdf"

LetsContinue:
        Exit Sub
Whoa:
        MsgBox Err.Number
        GoTo LetsContinue

End Sub