Problem with Range reference - Odd error

[Kc-Mass]

I have an Excel module that produces an error every other time run. If you
unload the module and start fresh it works perfectly. The second time it
fails; the third time it works perfectly, etc etc. It all revolves around a
reference to a range. I am new to Excel VBA and am perhaps doing the whole
reference wrong. I am now using range(Cells(),Cells())).

The module has a function that finds a certain value in column "A" and then
defines a range above that. At first I tried creating a variable like
"B2:F2382" complete with quotes and put that in a range reference like Set
rngOne = the variable, but Excel would have none of that.

When the module bombs it bombs on the line :
Set srcRange = xlBook1.Worksheets(strSheet1).Range(Cells(2, 2),
Cells(lngBottom, 6))

Any Ideas appreciated

Thx
Code follows



Sub MoveData()
'Set an instance of Excel and pointers for workbooks and sheets
Dim xlApp As Excel.Application
Dim xlBook1 As Excel.WorkBook
Dim xlBook2 As Excel.WorkBook
Dim xlSheet1 As Excel.Worksheet
Dim xlSheet2 As Excel.Worksheet
Dim strPath1 As String
Dim strPath2 As String
Dim strWBFile1 As String
Dim strWBFile2 As String
Dim strWholeFName1 As String
Dim strWholeFName2 As String

Dim lngBottom As Long
Dim srcRange As Range
Dim trgRange As Range
Dim strQuote As String
Dim strSheet1 As String
Dim strSheet2 As String

strPath1 = "C:\ApplicationDev\"
strWBFile1 = "LSPT Input_Northeast_12182006.xls"
strWBFile2 = "LSPTTemplate_Northeast_12182006.xls"
strWholeFName1 = strPath1 & strWBFile1
strWholeFName2 = strPath1 & strWBFile2
strSheet1 = "11i Catalyst Mismatch- 11i"
strSheet2 = "LSPT- Project Data"
Set xlApp = New Excel.Application
Set xlBook1 = xlApp.Workbooks.Open(strWholeFName1)
Set xlBook2 = xlApp.Workbooks.Open(strWholeFName2)
xlApp.Visible = True
Set xlSheet1 = xlBook1.Worksheets(strSheet1)
Set xlSheet2 = xlBook2.Worksheets(strSheet2)
xlSheet1.Activate
Debug.Print Now()

lngBottom = FindTopofZeroes(xlBook1, 3) - 1
Debug.Print lngBottom

Set srcRange = xlBook1.Worksheets(strSheet1).Range(Cells(2, 2),
Cells(lngBottom, 6))
Set trgRange = xlBook2.Worksheets(strSheet2).Range("B2")
xlSheet1.Activate
srcRange.Copy trgRange
Debug.Print Now()

'Lets do the cleanup
'Excel often won't close successfully without being made visible
xlApp.Visible = True
'Release the objects.
Set srcRange = Nothing
Set trgRange = Nothing
Set xlApp = Nothing
Set xlBook1 = Nothing
Set xlBook2 = Nothing
Set xlSheet1 = Nothing
Set xlSheet2 = Nothing
xlApp.Quit
End Sub


Function FindTopofZeroes(WB As WorkBook, WS As Long)
Dim rngA As Range
Dim lngHold As Long
With WB.Worksheets(WS).Columns(1)
Set rngA = .Find(What:="0.0.0.0.0", _
LookIn:=xlValues, _
Lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext)
If rngA Is Nothing Then
FindTopofZeroes = 0
Else
FindTopofZeroes = rngA.Row
lngHold = rngA.Row
End If
End With
End Function


Function FindLastUsedRow(WB As WorkBook, WS As Long, Column1 As Long)
Dim rngA As Range
Dim lngHold As Long
With WB.Worksheets(WS).Columns(Column1)
Set rngA = .Find(What:=WhatWant, _
SearchDirection:=xlPrevious, _
LookIn:=xlValues, _
SearchOrder:=xlByRows)
If rngA Is Nothing Then
FindLastUsedRow = 0
Else
FindLastUsedRow = rngA.Row
lngHold = rngA.Row
End If
End With
End Function

 

[Peter T]

I've only briefly glanced your code but this is not right -

'Lets do the cleanup
'Excel often won't close successfully without being made visible
xlApp.Visible = True
'Release the objects.
Set srcRange = Nothing
Set trgRange = Nothing
Set xlApp = Nothing
Set xlBook1 = Nothing
Set xlBook2 = Nothing
Set xlSheet1 = Nothing
Set xlSheet2 = Nothing
xlApp.Quit
End Sub

Destroy your objects in reverse order created

Set srcRange = Nothing
Set trgRange = Nothing
Set xlSheet1 = Nothing
Set xlSheet2 = Nothing
Set xlBook1 = Nothing
Set xlBook2 = Nothing
xlApp.Quit
Set xlApp = Nothing

I take it you are automating Excel, if you do it this way shouldn't be
becessary to make Excel visible before your cleanup. Actually, as all your
objects appear to have been declared in Sub MoveData() I think the only
thing you need to do is -
xlApp.Quit
The objects should be destroyed very cleanly by VB(?) when they go out of
scope - providing xl is not trying to show any user dialogs such as "do you
want to save the workbook(s)". Avoid that by ensuring the wb's are saved (or
..Saved = true) or close each with 'false' before the quit.

I haven't looked enough to know if this will solve you problems but I'd
start with the above first.

Ah - just noticed before posting

Set srcRange = xlBook1.Worksheets(strSheet1).Range(Cells(2, 2),
Cells(lngBottom, 6))

You need to qualify .Cells

With xlBook1.Worksheets(strSheet1)
.Range(.Cells(2, 2), .Cells(lngBottom, 6))
End with

Regards,
Peter T

 

[Peter T]

typo -

You need to qualify .Cells

With xlBook1.Worksheets(strSheet1)
.Range(.Cells(2, 2), .Cells(lngBottom, 6))
End with

With xlBook1.Worksheets(strSheet1)
Set srcRange = .Range(.Cells(2, 2), .Cells(lngBottom, 6))
End with

Peter T

<snip>

 

[Kc-Mass]

Peter Great job. Solved my problem and taught me something.
You folks are always terrific, on point and responsive. A great resource!

Many Thanks
Kevin C