vb.net VB在excel中查找和替换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17852195/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VB find and replace in excel
提问by slyclam
I am using VB to populate data in excel. They are populating in column A, row 1 to 3000.
我正在使用 VB 在 excel 中填充数据。它们填充在 A 列的第 1 行到 3000 行中。
Here's a sample portion:
这是一个示例部分：
H'03F8
H'03FD
H'4404
H'0812
... and so on
Now what I want to do is to find all strings starting with H' and remove all occurrences of it in the sheet. So the result would be like:
现在我想要做的是找到所有以 H' 开头的字符串并删除它在工作表中的所有出现。所以结果会是这样的：
03F8
03FD
4404
0812
... and so on
For this here is the code I'm using:
为此，这是我正在使用的代码：
Dim xl = Microsoft.VisualBasic.CreateObject("Excel.Application")
Dim wb = xl.Workbooks.Add()
Dim sheet = wb.ActiveSheet
Private Sub find_replace()
Dim myRange As Range
myRange = sheet.Range("A1:A3000")
For Each row In myRange
sheet.substitute(myRange, "H'", "")
Next
End Sub
But it gives an error while running: MissingMemberExeception was unhandled. and in the details it shows: Public member 'substitute' on type 'Worksheet' not found.
但是在运行时出现错误：MissingMemberException was unhandled。并在详细信息中显示：未找到类型“工作表”上的公共成员“替代”。
I'm using VB 2010 Express. Please help.
我正在使用 VB 2010 Express。请帮忙。
回答by dotNET
Simply use the following VBA:
只需使用以下 VBA：
sheet.Range("A1", "A3000").Replace "H'", "'"
回答by matzone
Try this ...
尝试这个 ...
Private Sub find_replace()
Dim myRange, r As Range
Dim s As String
myRange = sheet.Range("A1:A3000")
For Each r In myRange
s = r.Value
r.Value = s.Substitute("H'", "" )
Next
End Sub
