Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 91
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 91
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 91
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 91
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. "Tom Ogilvy" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime error '1004' application or object defined error | Excel Programming | |||
runtime error 13 - type mismatch error in Excel 97 on Citrix | Excel Programming | |||
runtime error '1004' application or object defined error. Please help | Excel Programming | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |