CODEWITHSUNDEEP
excelvba
Peter Lynch
Peter Lynch

Reputation: 129

VBA error with ISTEXT function in IF statement

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

Error 424 - Object Required

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

Upvotes: 2

Views: 20334

Answers (4)

CLR
CLR

Reputation: 12279

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

Upvotes: 0

Dy.Lee
Dy.Lee

Reputation: 7567

Using Variant is so fast.

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

Upvotes: 0

Jakob Busk Sørensen
Jakob Busk Sørensen

Reputation: 6091

The IsText() method should not be called with a ., but rather using (), like this:

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

Upvotes: 0

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

IsText is a method of the WorksheetFunction class.

You have got your syntax wrong, the correction would be:

If WorksheetFunction.IsText(Sheets("Strategies").Cells(i, 6)) = True Then

Upvotes: 2

Related Questions