Problem with cells.Find within a loop

[jowatkins]

Hi,

I'm having a problem with the following code. Basically, it keeps
falling over whenever I try to make the FIND bit run within a loop.
I'm now getting an error message "Object Required" which refers again
to the line the FIND bit is in.

Here's the code:

Sub copyProjectDates()
Dim sourceFile As Workbook
Dim DestFile As Workbook
Dim copyRange As Range
Dim destRange As Range
Dim foundCell As Range
Dim c As Range
Dim searchData As String

'ProjectRange = Range("B4:B42")
Set sourceFile = GetObject("H:\Schedule\Projects Overview.xls")
For Each c In Range("B4:B42")

searchData = c.Value
With sourceFile.Sheets("Sheet1")
Set foundCell = Cells.Find(What:=searchData, _
After:=[A1], _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
If Not foundCell Is Nothing Then
foundCell.Interior.Color = RGB(255, 0, 0)
Else
c.Interior.Color = RGB(0, 0, 255)
End If
End With
Next
sourceFile.Close SaveChanges:=False
End Sub

Any ideas gratefully received, Cheers, Jo

 

[Guest]

I don't see where you've initialized the Range
variable "c". Unless there's more to this code, I'd say
that's your problem.

-----Original Message-----

Hi,

I'm having a problem with the following code. Basically, it keeps
falling over whenever I try to make the FIND bit run within a loop.
I'm now getting an error message "Object Required" which refers again
to the line the FIND bit is in.

Here's the code:

Sub copyProjectDates()
Dim sourceFile As Workbook
Dim DestFile As Workbook
Dim copyRange As Range
Dim destRange As Range
Dim foundCell As Range
Dim c As Range
Dim searchData As String

'ProjectRange = Range("B4:B42")
Set sourceFile = GetObject("H:\Schedule\Projects Overview.xls")
For Each c In Range("B4:B42")

searchData = c.Value
With sourceFile.Sheets("Sheet1")
Set foundCell = Cells.Find(What:=searchData, _
After:=[A1], _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
If Not foundCell Is Nothing Then
foundCell.Interior.Color = RGB(255, 0, 0)
Else
c.Interior.Color = RGB(0, 0, 255)
End If
End With
Next
sourceFile.Close SaveChanges:=False
End Sub

Any ideas gratefully received, Cheers, Jo


------------------------------------------------
[/url]
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step

guide to creating financial statements

 

[Dick Kusleika]

Jo

Remove the .Activate at the end of the Find line. If it doesn't find the
cell, foundCell will be Nothing and issuing an Activate method on Nothing
will give you that error.

Another note: You are using a with statement, but you're not preceding your
dependent properties with a period

With sourceFile.Sheets("Sheet1")
Set FoundCell = Cells.Find(...)
End With

should be

With sourceFile.Sheets("Sheet1")
Set FoundCell = .Cells.Find(...)
End With

Note the period before Cells.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

Hi,

I'm having a problem with the following code. Basically, it keeps
falling over whenever I try to make the FIND bit run within a loop.
I'm now getting an error message "Object Required" which refers again
to the line the FIND bit is in.

Here's the code:

Sub copyProjectDates()
Dim sourceFile As Workbook
Dim DestFile As Workbook
Dim copyRange As Range
Dim destRange As Range
Dim foundCell As Range
Dim c As Range
Dim searchData As String

'ProjectRange = Range("B4:B42")
Set sourceFile = GetObject("H:\Schedule\Projects Overview.xls")
For Each c In Range("B4:B42")

searchData = c.Value
With sourceFile.Sheets("Sheet1")
Set foundCell = Cells.Find(What:=searchData, _
After:=[A1], _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
If Not foundCell Is Nothing Then
foundCell.Interior.Color = RGB(255, 0, 0)
Else
c.Interior.Color = RGB(0, 0, 255)
End If
End With
Next
sourceFile.Close SaveChanges:=False
End Sub

Any ideas gratefully received, Cheers, Jo

creating financial statements

 

[jowatkins]

thanks, Dick, this worked a treat!

 

[Dick Kusleika]

Jo

You're welcome.

Dick

thanks, Dick, this worked a treat!

creating financial statements