×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel VBA 7.1 Selection.Find Throwing an Error

Excel VBA 7.1 Selection.Find Throwing an Error

Excel VBA 7.1 Selection.Find Throwing an Error

(OP)
I don't know why I'm getting an error at the line that says Set RgFound
[code]

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]

RE: Excel VBA 7.1 Selection.Find Throwing an Error

Hi,

Selection is a Range object, so...

CODE

Set rgFound = Selection.Find(CurrFileName) 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel VBA 7.1 Selection.Find Throwing an Error

Note that:
1) you refer to non-initialised object. After Set rgFound = Selection.Find(CurrFileName) you call FoundCell: Range(FoundCell.End(xlDown)).Select.
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 Select method returns nothing, so execune the rest of code conditionally: In Not rgFound Is Nothing Then.

combo

RE: Excel VBA 7.1 Selection.Find Throwing an Error

(OP)
Thank you! This is working

[code]

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]

RE: Excel VBA 7.1 Selection.Find Throwing an Error

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


---- Andy

There is a great need for a sarcasm font.

RE: Excel VBA 7.1 Selection.Find Throwing an Error

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

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close