Excel VBA 7.1 Selection.Find Throwing an Error

[BeckyMc]

I don't know why I'm getting an error at the line that says Set RgFound

Sub CheckFileFormat()

Dim Array1(200) As String
Dim Array2(200) As String
Dim CurrFileName As String
Dim Numrows As Integer
Dim I As Integer

Dim rgFound As Range

CurrFileName = Range("A2").Value
MsgBox CurrFileName

If Range("F2").Value <> "" Then

    ActiveWorkbook.Sheets("ColumnHeadings").Activate
    
    Range("A1").End(xlToRight).Select
    
    Set rgFound = Range(Selection).Find(CurrFileName)
    Range(FoundCell.End(xlDown)).Select
    Numrows = Selection.Rows.Count
    
    For I = 1 To Numrows
    
    
    Next I

End If


End Sub

[code]

 

[SkipVought]

Hi,

Selection is a Range object, so...

Set rgFound = Selection.Find(CurrFileName)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

Note that:
1) you refer to non-initialised object. After [tt]Set rgFound = Selection.Find(CurrFileName)[/tt] you call FoundCell: [tt]Range(FoundCell.End(xlDown)).Select[/tt].
2) if rgFound and FoundCell are to be the same cells, then Numrows = Selection.Rows.Count =1, always, as you search and select cells in single row,
3) if nothing found in selected range, the [tt]Select[/tt] method returns nothing, so execune the rest of code conditionally: [tt]In Not rgFound Is Nothing Then[/tt].



combo

 

[BeckyMc]

Thank you! This is working

Sub CheckFileFormat()

Dim Array1(200) As String
Dim Array2(200) As String
Dim CurrFileName As String
Dim Numrows As Integer
Dim I As Integer

Dim rgFound As Range

CurrFileName = Range("A2").Value


If Range("F2").Value <> "" Then

    ActiveWorkbook.Sheets("ColumnHeadings").Activate
    
    Range("A1").End(xlToRight).Select
    
    Set rgFound = Selection.Find(CurrFileName)
    Range(rgFound, rgFound.End(xlDown)).Select
    Numrows = Selection.Rows.Count
    
    For I = 1 To Numrows
    
    
    Next I

End If


End Sub

[code]

 

[Andrzejek]

Becky,
Please use the Preview button before Submitting your post(s).


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

I'd do it without Activate and Select...

If Range("F2").Value <> "" Then

   With ActiveWorkbook.Sheets("ColumnHeadings")

      Set rgFound = .Range("A1").End(xlToRight).Find(CurrFileName)

      If Not rgFound Is Nothing Then

         NumRows = .Range(rgFound, rgFound.End(xlDown)).Rows.Count

         For I = 1 To Numrows


         Next 

      End If 

   End With

End If

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein