My guess is that the line

If Cell.Value = ref Then

never evaluates to true (a match is never found to ref)

--
Regards,
Tom Ogilvy


"andim" wrote:

Hello All

I am getting runtime error 91 on execution of some of my code. I will post
all the code and show where the error is showing in debug:



Private Sub CommandButton3_Click()
lm = ActiveWorkbook.Name 'Names Learnmacs "lm"

Dim ref As Range, gethighest As Range ' sets vars ref for reference and
gethighest for range to search for highest date
Set ref = Worksheets("Sheet1").Range("A19") ' Assigns A19 (ref cell) to ref
variable
Set gethighest = Worksheets("Sheet1").Range("A20:C23") ' Assigns range
A20:C23 to gethighest variable
highest = Application.WorksheetFunction.Max(gethighest) ' Uses MAX worksheet
function to assign highest number in range to highest variable

Workbooks.Open ("C:\Documents and Settings\Andrew\My Documents\Serious
Stuff\TCSpreadsheets\Convoluted.xls") 'Opens Convoluted
cd = ActiveWorkbook.Name ' Names Convoluted "cd" for closure later

Dim cdrefcol As Range ' var to hold range to find ref in cd
Dim cellad As Range ' var to hold cell address of match to ref var
Dim Cell As Object

Set cdrefcol = Range("A10:A12") ' assign search area in cd to cdrefcol

For Each Cell In cdrefcol
If Cell.Value = ref Then
If cellad Is Nothing Then
Set cellad = Range(Cell.Address)
Else
Set cellad = Union(cellad, Range(Cell.Address))
End If
End If
Next

cellad.Offset(0, 2).Value = highest <THIS IS WHERE THE DEBUGGER POINTS

Workbooks(cd).Close SaveChanges:=True ' Closes Convoluted and saves (cd)
End Sub

As I am sure you have gathered the code takes the highest value in a range
of cells and a reference relating to them then opens another file searches a
range for a match to the reference offsets by 2 and pastes the highest value.
The references match in the files but the debugger says that there is no
value in cellad variable.

This code works when it is only on one sheet and no other needs be opened.

Please help.
Thanks.