Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Range reference - Odd error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Range reference - Odd error
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 "Kc-Mass" <connearney_AT_comcast_PERIOD_net wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Range reference - Odd error
typo -
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 With xlBook1.Worksheets(strSheet1) Set srcRange = .Range(.Cells(2, 2), .Cells(lngBottom, 6)) End with Peter T <snip |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Range reference - Odd error
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 "Peter T" <peter_t@discussions wrote in message ... typo - 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 With xlBook1.Worksheets(strSheet1) Set srcRange = .Range(.Cells(2, 2), .Cells(lngBottom, 6)) End with Peter T <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Message - Input Range must be a contiguous Reference Help!!! | Excel Discussion (Misc queries) | |||
Named range as chart data reference (error) | Charts and Charting in Excel | |||
Conflict with valid range reference error | Excel Discussion (Misc queries) | |||
error using a reference in a range | Excel Programming | |||
Reference range in formula problem | Excel Programming |