在excel中将字符串写入单元格 [英] Writing a string to a cell in excel

问题描述

我正在尝试向A1单元格写一个值，但是出现以下错误：应用程序定义或对象定义的错误'1004'

我已经在网上尝试了许多解决方案，但没有一个是正常的。我正在使用excel 2007，文件扩展名是.xlsm。

我的代码如下：

  Sub varchanger（）
错误GoTo Whoa 
 Dim TxtRng As Range 
 
工作表（Game）。激活
 ActiveSheet。 Unprotect 
 
设置TxtRng = ActiveWorkbook.Sheets（游戏）。单元格（1，1）
 TxtRng.Value =SubTotal
 
'Worksheets（游戏）范围（A1）=Asdf
 
 LetsContinue：
退出子
哇：
 MsgBox Err.number 
 Resume LetsContinue 
 End Sub 
  

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

解决方案

我想你可能会被绊倒在工作表保护。我简化了你的代码，并明确地设置了对工作簿和工作表对象的引用。在您的示例中，当您设置TxtRng对象时，您明确地引用了工作簿和工作表，而不是在取消保护工作表时。

尝试这样：

  Sub varchanger（）
 
 Dim wb As Workbook 
 Dim ws As Worksheet 
 Dim TxtRng As Range 
 
设置wb = ActiveWorkbook 
设置ws = wb.Sheets（Sheet1）
'或ws.Unprotect密码：=yourpass
 ws .Unprotect 
 
设置TxtRng = ws.Range（A1）
 TxtRng.Value =SubTotal
'http://stackoverflow.com/questions/8253776/worksheet -protection-set-using-ws-protect-but-doesnt-unprotect-using-the-menu 
'或ws.Protect Password：=yourpass
 ws.Protect 
 
 End Sub 
  

如果我使用 ws.Unprotect 注释掉，我得到一个运行时错误1004.（假设我保护了工作表并且范围被锁定。）取消注释行允许代码运行正常。

注意：

1. 写入范围后，我重新设置工作表保护。我假设你想要这样做，如果你的首页保护表。如果您进一步处理后再重新设置保护，则需要删除该行。


2. 我删除了错误处理程序。 Excel错误消息给你比Err.number更多的细节。你可以把它重新放在一旦你得到你的代码工作，并显示任何你想要的。显然，您可以使用Err.Description。


3. 单元格（1，1）符号可能会导致大量的悲伤。小心使用它。 范围（A1）对于人类来说很容易解析，并且倾向于防止额头上的错误。

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

解决方案

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:

1. 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.
2. 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.
3. 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.

这篇关于在excel中将字符串写入单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！