Set range error

[Guest]

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.

 

[Guest]

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.

 

[Ron de Bruin]

Remove one )

................Cells(lrow, lcol)))

And show your code

 

[Dave Peterson]

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!

 

[Guest]

Here is the code. It errors out at "Set SourceRange1".




Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName
As String) As Long

*******************************************
Public Sub ChDirNet(szPath As String)
' For Sub GetData & OTHER SUBS IN FORM
' Rob Bovey
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub

*******************************************
Function LastRow(sh As Worksheet)
' For Sub GetData
' 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)
' For Sub GetData
' 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

**********************************************
Sub m02_GetData()
' This Sub uses 4 functions:
' 1. Private Declare Function SetCurrentDirectoryA (at top of module)
' 2. Public Sub ChDirNet(szPath As String)
' 3. Function LastRow(sh As Worksheet)
' 4. Function LastCol(sh As Worksheet)
' Opens each Order Status Spreadsheet in succession and copies to blank
template
'
MsgBox "Please select all files at once" & vbNewLine & vbNewLine & vbNewLine
On Error GoTo ErrorHandler
Dim SaveDriveDir As String
Dim MyPath As String 'Dim FilesInPath As String
Dim MyFiles() As Variant
Dim SourceRcount1, SourceRcount2 As Long
Dim Fnum As Long
Dim basebook, mybook As Workbook
Dim sourceRange1, sourceRange2 As Range
Dim destrange1, destrange2 As Range
Dim rnum1, rnum2 As Long
Dim lrow1, lrow2 As Long
Dim lcol1, lcol2 As Long

SaveDriveDir = CurDir

'Fill in the path\folder where the files are
'on your machine : MyPath = "C:\Data" or on a network :
ChDirNet "\\Sling\taiwan\Order_Status"


MyFiles = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),
*.xls", MultiSelect:=True)

MsgBox "Processing will take about five minutes. That'll be at about " &
Format(DateAdd("n", 5, Now), "medium time") & ", ok? " & vbNewLine &
vbNewLine _
& "Be sure to click OK before you go!"


If IsArray(MyFiles) Then
Application.ScreenUpdating = False

Set basebook = ActiveWorkbook
rnum1 = 1
rnum2 = 1

On Error GoTo ErrorHandler 'CleanUp

'Loop through all files in the array(myFiles)
For Fnum = LBound(MyFiles) To UBound(MyFiles)

Set mybook = Workbooks.Open(MyFiles(Fnum))

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
ActiveSheet.DisplayPageBreaks = False

lrow1 = LastRow(mybook.Sheets(1))
lrow2 = LastRow(mybook.Sheets(2))
lcol1 = LastCol(mybook.Sheets(1))
lcol2 = LastCol(mybook.Sheets(2))

Set sourceRange1 = mybook.Worksheets(1).Range(Cells(1, 1),
Cells(lrow1, lcol1))
'Set sourceRange1 = mybook.Worksheets(1).Range("A1:AA" & lrow1)
Set sourceRange2 = mybook.Worksheets(2).Range(Cells(1, 1),
Cells(lrow2, lcol2))
'Set sourceRange2 = mybook.Worksheets(2).Range("A1:AA" & lrow2)
SourceRcount1 = sourceRange1.Rows.Count
SourceRcount2 = sourceRange2.Rows.Count
Set destrange1 = basebook.Worksheets(1).Range("A" & rnum1)
Set destrange2 = basebook.Worksheets(2).Range("A" & rnum2)

sourceRange1.Copy destrange1
sourceRange2.Copy destrange2

rnum1 = rnum1 + SourceRcount1
rnum2 = rnum2 + SourceRcount2
mybook.Close savechanges:=False
Next Fnum
Else: Exit Sub
Exit Sub
End If

CleanUp:
Application.ScreenUpdating = True
ChDirNet SaveDriveDir

ErrorHandlerNext:
Exit Sub

ErrorHandler:
Err.Raise 1001
'MsgBox "Error " & Err.Number & "; " & Err.Description
'Resume ErrorHandlerNext

End Sub

 

[Guest]

Actually, Dave's suggestion below worked! But, thank you for your reply!
Cheers!

 

[Guest]

Thank you, Dave. You solved my problem!
And thanks so much for your explanation.
Many, many thanks.