IF 语句中 ISTEXT 函数的 VBA 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44803736/
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
VBA error with ISTEXT function in IF statement
提问by Peter Lynch
Just started using VBA and I'm basically looking to check if an item in a column is text and then copy it to another sheet in a row. I get stopped at the first line of the IF statement with
刚开始使用 VBA，我基本上是想检查列中的项目是否为文本，然后将其复制到一行中的另一个工作表中。我在 IF 语句的第一行停了下来
Error 424 - Object Required
错误 424 - 需要对象
Have looked at a few of these questions and websites and can't seem to figure out where I've gone wrong.
查看了其中一些问题和网站，似乎无法弄清楚我哪里出错了。
Thanks very much.
非常感谢。
Sub Copier()
Dim i As Integer
Dim j As Integer
j = 1
For i = 1 To 100
If IsText.Sheets("Strategies").Cells(i, 6) = True Then
Sheets("Strategies").Select
Cells(i, 6).Select
Selection.Copy
Sheets("Stats").Select
Cells(2, j).Select
Sheets("Stats").Paste
j = j + 1
End If
Next i
End Sub
回答by Robin Mackenzie
回答by BuildItBusk
The IsText()method should not be called with a ., but rather using (), like this:
IsText()不应使用 a 调用该方法.，而应使用()，如下所示：
For i = 1 To 100
s = Sheets("Strategies").Cells(i, 6).Value
If Application.WorksheetFunction.IsText(s)Then
Sheets("Strategies").Select
Cells(i, 6).Select
Selection.Copy
Sheets("Stats").Select
Cells(2, j).Select
Sheets("Stats").Paste
j = j + 1
End If
Next i
回答by Dy.Lee
Using Variant is so fast.
使用 Variant 非常快。
Sub test()
Dim i As Integer
Dim j As Integer
Dim Wf As WorksheetFunction
Dim fromWs As Worksheet, ToWs As Worksheet
Dim vDB, vR()
Set fromWs = Sheets("Strategies")
Set ToWs = Sheets("Stats")
Set Wf = WorksheetFunction
vDB = fromWs.Range("f1").Resize(100)
For i = 1 To UBound(vDB, 1)
If Wf.IsText(vDB(i, 1)) Then
j = j + 1
ReDim Preserve vR(1 To j)
vR(j) = vDB(i, 1)
End If
Next i
If j > 0 Then
ToWs.Range("a2").Resize(1, j) = vR
End If
End Sub
回答by CLR
You could tidy the whole thing up as follows:
你可以把整个事情整理如下：
Dim i As Integer, j As Integer
Dim sourcesheet As Worksheet, targetsheet As Worksheet
j = 1
Set sourcesheet = Sheets("Strategies")
Set targetsheet = Sheets("Stats")
With sourcesheet
For i = 1 To 100
s = .Cells(i, 6).Value
If Application.WorksheetFunction.IsText(s) Then
.Cells(i, 6).Copy targetsheet.Cells(2, j)
j = j + 1
End If
Next i
End With
