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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~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

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~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
.

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.

"jowatkins" wrote in 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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~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

thanks, Dick, this worked a treat!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~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

Jo

You're welcome.

Dick

"jowatkins" wrote in message
...

thanks, Dick, this worked a treat!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~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