Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set range error
Hi, How come this works: Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) but this doesn't: Set sourceRange = mybook.Worksheets(1).Range(Cells(1, 1), Cells(lrow, lcol))) I get object defined error. here are the functions I'm using: Function LastRow(sh As Worksheet) ' Find the last real row On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function LastCol(sh As Worksheet) ' Find the last real column On Error Resume Next LastCol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function lrow and lcol both dimmed as long in my sub. Thank you much. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set range error
The code appears to be ok and it worked for me the functions and the code,
apart from an extra closing bracket. The only difference is that I used ActiveWorkbook. Set SourceRange = ActiveWorkbook.Worksheets(1).Range(Cells(1, 1), Cells(lrow, lcol)) Have you option explicit on. Step through the code and see where it is you are breaking down. you will probably find that lcol is the killer as that is the main difference between the two. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "justme" wrote: Hi, How come this works: Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) but this doesn't: Set sourceRange = mybook.Worksheets(1).Range(Cells(1, 1), Cells(lrow, lcol))) I get object defined error. here are the functions I'm using: Function LastRow(sh As Worksheet) ' Find the last real row On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function LastCol(sh As Worksheet) ' Find the last real column On Error Resume Next LastCol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function lrow and lcol both dimmed as long in my sub. Thank you much. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set range error
Remove one )
................Cells(lrow, lcol))) And show your code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "justme" wrote in message ... Hi, How come this works: Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) but this doesn't: Set sourceRange = mybook.Worksheets(1).Range(Cells(1, 1), Cells(lrow, lcol))) I get object defined error. here are the functions I'm using: Function LastRow(sh As Worksheet) ' Find the last real row On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function LastCol(sh As Worksheet) ' Find the last real column On Error Resume Next LastCol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function lrow and lcol both dimmed as long in my sub. Thank you much. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set range error
Unqualified ranges usually refer to the activesheet. If mybook.worksheets(1)
isn't the active sheet, then your second example will blow up real good. You could use Set sourceRange = mybook.Worksheets(1) _ .Range(mybook.Worksheets(1).Cells(1, 1), _ mybook.Worksheets(1).Cells(lrow, lcol))) But it's less typing and easier to understand to do something like: with mybook.worksheets(1) Set sourceRange = .Range(.Cells(1, 1), .Cells(lrow, lcol))) end with The dots in front of the range objects (range() and cells()) mean that they belong to the object in the previous With statement--this time mybook.worksheets(1). === Just to note... When you use the same kind of unqualified ranges behind a worksheet, then that unqualified range won't belong to the activesheet--they'll belong to the sheet that owns the code. I find it always less painful to qualify the ranges. Even if I have to do: dim wks as worksheet set wks = activesheet with wks ... By using a variable that represents a worksheet, I get VBA's intellisense, too! justme wrote: Hi, How come this works: Set sourceRange = mybook.Worksheets(1).Range("A2:IV" & lrow) but this doesn't: Set sourceRange = mybook.Worksheets(1).Range(Cells(1, 1), Cells(lrow, lcol))) I get object defined error. here are the functions I'm using: Function LastRow(sh As Worksheet) ' Find the last real row On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error GoTo 0 End Function Function LastCol(sh As Worksheet) ' Find the last real column On Error Resume Next LastCol = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 End Function lrow and lcol both dimmed as long in my sub. Thank you much. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set range error
Actually, Dave's suggestion below worked! But, thank you for your reply!
Cheers! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set range error
Thank you, Dave. You solved my problem!
And thanks so much for your explanation. Many, many thanks. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript out of range error - save copy error | Excel Programming | |||
Subscript out of range error - save copy error | Excel Programming | |||
Range.Select error and screen maximizing error in Workbook_Open() | Excel Programming | |||
Range.Select error and screen maximizing error in Workbook_Open() | Excel Programming | |||
Type Mismatch error & subscript out of range error | Excel Programming |