SpecialCells Loop not working

[WindsorKnot]

Hi,

I'm getting an error in my code below.

I basically want to determine if the values in column F on the "50000 sheet are > 50000 and if so to copy them into the "Storage" sheet in column A and loop until finished

The error is highlighted in red below- Object Variable or with Block Variable not set

Sub GetValue2()
    Dim I As Integer
    Dim Spcell As Range
    I = 2
    Dim R As Worksheet
    Set R = Sheets("50000")
     Do Until R.Range("F1").Value = "Total"
        [COLOR=red]If Spcell.SpecialCells(xlCellTypeConstants, xlValues) > 50000 Then[/COLOR]
            Sheets("storage").Cells(I, 1).Value = Spcell.Value
        End If
        I = I + 1
    Loop
End Sub

 

[xld]

Try

Sub GetValue2()
    Dim I As Integer
    Dim Spcell As Range
    Dim cell As Range
    I = 2
    Dim R As Worksheet
    Set R = Sheets("50000")
    Set Spcell = R.Range("F;F")
    For Each cell In Spcell.SpecialCells(xlCellTypeConstants, xlValues)
        If cell.Range("F1").Value = "Total" Then Exit For
        If Spcell > 50000 Then
        
            Sheets("storage").Cells(I, 1).Value = Spcell.Value
            I = I + 1
        End If
    Next cell
End Sub

 

[WindsorKnot]

The error is now 1004 "Unable to get the specialcells property of the range class"

Any follow-up ideas?

 

[xld]

See if this is better

Sub GetValue2()
    Dim I As Integer
    Dim Spcell As Range
    Dim cell As Range
    I = 2
    Dim R As Worksheet
    Set R = Sheets("50000")
    Set Spcell = R.Range("F:F")
    For Each cell In Spcell.SpecialCells(xlCellTypeConstants)
        If cell.Range("F1").Value = "Total" Then Exit For
        If cell.Value > 50000 Then
        
            Sheets("storage").Cells(I, 1).Value = Spcell.Value
            I = I + 1
        End If
    Next cell
End Sub