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

User · Answer

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

User · Answer

replace Range  A1      Asdf  with Range  A1   value    Asdf

User · Answer

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

User · Answer

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

[vba] Writing a string to a cell in excel

Examples related to vba

Examples related to excel

Examples related to excel-2007