在excel中为单元格写入一个字符串。

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

我正在尝试向“A1”单元格写入一个值，但是我得到了以下错误:

Application-defined or object-defined error '1004'

应用程序定义的或对象定义的错误'1004'

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

我在网上尝试过很多解决方案，但没有一个有效。我使用excel 2007，文件extensiton是。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

编辑:当我得到错误后，如果我点击警告图标，然后选择显示计算步骤它正常工作

4 个解决方案

#1

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.

我想你可能是被床单保护罩给绊倒了。我简化了您的代码，并显式地设置了对工作簿和工作表对象的引用。在您的示例中，在设置TxtRng对象时显式地引用工作簿和工作表，而在不保护工作表时则不引用。

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://*.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.

如果我用ws运行sub。Unprotect注释掉了，我得到一个运行时错误1004。(假设我保护了床单，并将范围锁定。)不注释这行代码可以正常运行。

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.
我删除了错误处理程序。Excel错误消息提供了比error .number更详细的信息。一旦你的代码开始工作并显示你想要的任何东西，你就可以把它放回去。显然你可以使用Err。描述。
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.
细胞(1,1)符号可以引起大量的悲伤。小心使用它。Range(“A1”)对人类来说更容易解析，并且可以防止头拍的错误。

#2

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

用Range("A1")替换Range("A1") = "Asdf"。值=“Asdf”

#3

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.

但是，工作表是一个集合，所以您不能引用“Game”。我想你需要使用Sheets对象。

#4

try this instead

试试这个相反

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

设置TxtRng = ActiveWorkbook.Sheets(“游戏”).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"
保存一个新的xlsm文件，并将其命名为“myfullyqualifi .xlsm”
Add a sheet with no protection and call it "mySheet"
添加一个没有保护的表单，并将其命名为“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

#1