Runtime error 91
Since the variable cd holds the cd workbook name:
Set cdrefcol = workbooks(cd).Range("A10:A12")
--
Regards,
Tom Ogilvy
"andim" wrote:
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" wrote:
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.
|