Writing a string to a cell in excel

Question

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

@LittleBobbyTables Thank you. This error is really freaking me out — knightrider, Jul 19 '12 at 20:01
I tried executing your *exact* code and it ran without any problems on both my Excel 2003 and Excel 2010. Sorry I don't have Excel 2007 to try. — James L., Jul 19 '12 at 20:10
I doubt it. I did a search for your error message on Bing and it turned up all sorts of errors -- all of which were syntax related. Have you tried stepping through your code via F8 to find the line on which the error occurs? Sometimes looking at the extra debug info in the Excel debugger when it throws the error can help narrow things down a bit. — James L., Jul 19 '12 at 20:16
error occurs in the following statement `TxtRng.Value = "SubTotal"` — knightrider, Jul 19 '12 at 20:17
You should put a breakpoint on the `TxtRng.value = "SubTotal"` line and inspect `TxtRng` before the assignment happens. It would be interesting to see if it is a valid object -- the one that you're expecting it to be. — James L., Jul 19 '12 at 20:20
I have checked col and row of TxtRng before the error occurs. These values are correct. — knightrider, Jul 19 '12 at 20:22
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/14151/discussion-between-james-l-and-knightrider) — James L., Jul 19 '12 at 20:22
Try declaring TxtRng `As Excel.Range` instead of just `Range`. If that doesn't work, try `Worksheets("Game").Select` instead of `Activate`. If that doesn't work, unprotect the workbook/sheet manually first. — JimmyPena, Jul 19 '12 at 20:45
How many files do you have open? Just the one? Try qualifying all of your worksheet references with a workbook: eg. `Worksheets("Game")` should be `ThisWorkbook.Worksheets("Game")`. Then comment out the error handlers and run it see what line is the issue. — Tim Williams, Jul 19 '12 at 21:37
@TimWilliams I only have one file open. Also `Set TxtRng = ActiveWorkbook.Sheets("Game").Cells(1, 1)` is working properly — knightrider, Jul 20 '12 at 04:16

score 18 · Accepted Answer · edited Mar 31 '16 at 08:30

I think you may be getting tripped up on the sheet protection. I streamlined your code a little and am explicitly setting references to the workbook and worksheet objects. In your example, you explicitly refer to the workbook and sheet when you're setting the TxtRng object, but not when you unprotect the sheet.

Try this:

Sub varchanger()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim TxtRng  As Range

    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("Sheet1")
    'or ws.Unprotect Password:="yourpass"
    ws.Unprotect

    Set TxtRng = ws.Range("A1")
    TxtRng.Value = "SubTotal"
    'http://stackoverflow.com/questions/8253776/worksheet-protection-set-using-ws-protect-but-doesnt-unprotect-using-the-menu
    ' or ws.Protect Password:="yourpass"
    ws.Protect

End Sub

If I run the sub with ws.Unprotect commented out, I get a run-time error 1004. (Assuming I've protected the sheet and have the range locked.) Uncommenting the line allows the code to run fine.

NOTES:

I'm re-setting sheet protection after writing to the range. I'm assuming you want to do this if you had the sheet protected in the first place. If you are re-setting protection later after further processing, you'll need to remove that line.
I removed the error handler. The Excel error message gives you a lot more detail than Err.number. You can put it back in once you get your code working and display whatever you want. Obviously you can use Err.Description as well.
The Cells(1, 1) notation can cause a huge amount of grief. Be careful using it. Range("A1") is a lot easier for humans to parse and tends to prevent forehead-slapping mistakes.

What was the error description? (the full error message, not just 1004) — Jon Crowell, Jul 21 '12 at 13:52

score 1 · Answer 2 · answered Jul 21 '12 at 01:48

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.

score 1 · Answer 3 · answered Apr 16 '15 at 20:01

1

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

answered Apr 16 '15 at 20:01

T. I. Troll

11
1

whytheq · Answer 4 · 2012-07-21T09:35:09.830

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

classic - please turn Excel off and on again; maybe even re-boot your machine. — whytheq, Jul 19 '12 at 20:24

Writing a string to a cell in excel

4 Answers4

Linked

Related