Runtime error 91

[andim]

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.

 

[Tom Ogilvy]

My guess is that the line

If Cell.Value = ref Then

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

 

[andim]

Hi Tom

You are right about that and here is the reason why -

The range of cells being searched is in the original file (lm). and I need
to search the cells A10:A12 in the new file (cd). I have tried activating the
new file but this doesn't work. Any Ideas?

 

[Tom Ogilvy]

Since the variable cd holds the cd workbook name:

Set cdrefcol = workbooks(cd).Range("A10:A12")

 

[andim]

That doesn't work but changing the line to

Set cdrefcol = Worksheets("Sheet1").Range("A10:A12")

allows the search to commence in the correct area.
What happens now though is that the highest variable is pasted in the
correct cell (cellad offset 2 along) in the wrong sheet (lm instead of cd).
I can't get it to populate in the cd sheet even though it is the active sheet.
Tricky problem this one - at least for me.