Excel VBA 循环单元格并替换其值
我正在尝试构建一个宏，该宏循环遍历一列单元格，并用该国家/地区的名称替换该单元格中的两个字母的国家/地区代码。但是，当我尝试运行宏时，出现“未找到对象”错误。
Sub ChangeCountryText()
'
' ChangeCountryText Macro
' Changes country codes
'
For counter = 2 To 20
Set curCell = ActiveSheet.Cells(counter, 1)
Select Case curCell.Text
Case "JP"
curCell.Text = "Japan"
Case "FR"
curCell.Text = "France"
Case "IT"
curCell.Text = "Italy"
Case "US"
curCell.Text = "United States"
Case "NL"
curCell.Text = "Netherlands"
Case "CH"
curCell.Text = "Switzerland"
Case "CA"
curCell.Text = "Canada"
Case "CN"
curCell.Text = "China"
Case "IN"
curCell.Text = "India"
Case "SG"
curCell.Text = "Singapore"
End Select
Next counter
End Sub
I am trying to build a macro that cycles through a column of cells and replaces a two letter country code in that cell with the name of that country. However I get an object not found error when I try to run the macro.
Sub ChangeCountryText()
'
' ChangeCountryText Macro
' Changes country codes
'
For counter = 2 To 20
Set curCell = ActiveSheet.Cells(counter, 1)
Select Case curCell.Text
Case "JP"
curCell.Text = "Japan"
Case "FR"
curCell.Text = "France"
Case "IT"
curCell.Text = "Italy"
Case "US"
curCell.Text = "United States"
Case "NL"
curCell.Text = "Netherlands"
Case "CH"
curCell.Text = "Switzerland"
Case "CA"
curCell.Text = "Canada"
Case "CN"
curCell.Text = "China"
Case "IN"
curCell.Text = "India"
Case "SG"
curCell.Text = "Singapore"
End Select
Next counter
End Sub
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
Text 属性是只读的 - 您无法设置它。分配给 Value 属性，它应该可以工作（例如
curCell.Value = "Japan"）The Text property is read-only - you can't set it. Assign to the Value property and it should work (e.g.
curCell.Value = "Japan")我确信您有充分的理由为此使用宏，但您可能想研究一下 LOOKUP 或 VLOOKUP 工作表函数，作为无需编写宏即可执行此类操作的方法。
I'm sure you have a great reason for using a macro for this, but you may want to look into the LOOKUP or VLOOKUP worksheet functions as a way to do something like this without writing a macro.
您应该能够通过在编辑器中单击宏文本的左侧并在该行上放置一个红点来进入调试器
然后您可以单步执行宏直到出现错误。
或者，您可以在宏的
顶部添加错误处理，并在末尾子添加之前添加
“未找到对象”可能来自 curCell.Text 调用（curCell 为 null、什么都没有或无效，因此在其上调用 .Text 会失败）或 ActiveSheet.Cells 调用（不确定是否会发生这种情况）
You should be able to enter the debugger by clicking to the left of your macro text in the editor and placing a red dot on the line
Then you can step through your macro until you get to the error.
Alternatively you can add error handling to your macro
at the top and before the end sub add
"Object not found" is likely from the curCell.Text call (curCell is null, nothing, or invalid, so calling .Text on it is failing) or the ActiveSheet.Cells call (not sure if this can happen)