Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Object Variable Not Set Error on Selection object
Hi,
I am export some MS ACCESS 2003 Query records onto an Excel Spread Sheet. One section of the code is select a range of cells and move the these cells by the columns into another location. I am using "SELECTION". Please see below codes. The problem is I can only run the module (written in MS ACCESS) once without error. After the 1st time, I closed the Excel spreadsheet (genereated by the 1st run), call the module again, I would run into this error when it hit the "Selection" object. It would error at "Selection.Clear". But if I closed MS ACCESS and restart, I can run the report without error. Error: Object Variable Not Set or With Object Variable Not Set ================================================== ================ Set rngCurr = wksNew.Range(wksNew.Cells(6, 9), _ wksNew.Cells(7 + rstProjects.RecordCount, rstProjects.Fields.Count - 2)) 'wksNew.Columns(sColRange).Cut 'wksNew.Columns("I:L").Cut 'rngCurr.Columns.Select Selection.Clear rngCurr.Select Selection.EntireColumn.Select appExcel.CutCopyMode = False Selection.Cut 'ActiveWindow.LargeScroll ToRight:=-2 Columns("C:C").Select Selection.Insert Shift:=xlToRight Set rngCurr = Nothing ================================================== =============== Please help... Thanks Jean |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Object Variable Not Set Error on Selection object
Jean,
Not quite sure what you are trying to do with all of the cuts and selects. Some general suggestions that should help... Don't select anything (with rare exceptions). Use range objects, do not use selection or activesheet or activecell ... Set rngToUse = wksNew.Columns(sColRange) If you don't use a range object then qualify it with the parent object ... wksNew.Columns("L:L") Remember that clearing a range will empty the clipboard. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Jean" wrote in message ... Hi, I am export some MS ACCESS 2003 Query records onto an Excel Spread Sheet. One section of the code is select a range of cells and move the these cells by the columns into another location. I am using "SELECTION". Please see below codes. The problem is I can only run the module (written in MS ACCESS) once without error. After the 1st time, I closed the Excel spreadsheet (genereated by the 1st run), call the module again, I would run into this error when it hit the "Selection" object. It would error at "Selection.Clear". But if I closed MS ACCESS and restart, I can run the report without error. 'Error: Object Variable Not Set or With Object Variable Not Set Set rngCurr = wksNew.Range(wksNew.Cells(6, 9), _ wksNew.Cells(7 + rstProjects.RecordCount, rstProjects.Fields.Count - 2)) 'wksNew.Columns(sColRange).Cut 'wksNew.Columns("I:L").Cut 'rngCurr.Columns.Select Selection.Clear rngCurr.Select Selection.EntireColumn.Select appExcel.CutCopyMode = False Selection.Cut 'ActiveWindow.LargeScroll ToRight:=-2 Columns("C:C").Select Selection.Insert Shift:=xlToRight Set rngCurr = Nothing Please help... Thanks Jean |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Object Variable Not Set Error on Selection object
Hi Jim
I am trying to cut columns in range "rngCurr" to insert cut columns at column "C:C". I don't want to use Selection. But don't know any other way. Please help. Thanks Jean "Jim Cone" wrote in message ... Jean, Not quite sure what you are trying to do with all of the cuts and selects. Some general suggestions that should help... Don't select anything (with rare exceptions). Use range objects, do not use selection or activesheet or activecell ... Set rngToUse = wksNew.Columns(sColRange) If you don't use a range object then qualify it with the parent object ... wksNew.Columns("L:L") Remember that clearing a range will empty the clipboard. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Jean" wrote in message ... Hi, I am export some MS ACCESS 2003 Query records onto an Excel Spread Sheet. One section of the code is select a range of cells and move the these cells by the columns into another location. I am using "SELECTION". Please see below codes. The problem is I can only run the module (written in MS ACCESS) once without error. After the 1st time, I closed the Excel spreadsheet (genereated by the 1st run), call the module again, I would run into this error when it hit the "Selection" object. It would error at "Selection.Clear". But if I closed MS ACCESS and restart, I can run the report without error. 'Error: Object Variable Not Set or With Object Variable Not Set Set rngCurr = wksNew.Range(wksNew.Cells(6, 9), _ wksNew.Cells(7 + rstProjects.RecordCount, rstProjects.Fields.Count - 2)) 'wksNew.Columns(sColRange).Cut 'wksNew.Columns("I:L").Cut 'rngCurr.Columns.Select Selection.Clear rngCurr.Select Selection.EntireColumn.Select appExcel.CutCopyMode = False Selection.Cut 'ActiveWindow.LargeScroll ToRight:=-2 Columns("C:C").Select Selection.Insert Shift:=xlToRight Set rngCurr = Nothing Please help... Thanks Jean |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Object Variable Not Set Error on Selection object
Jean,
Where you would use the term "Selection", just replace it with the actual range object or the range address.... rngCurr.Select Selection.Clear becomes... rngCurr.Clear If the cell formatting is not necessary then assigning the values from one range to another range is the easiest. The ranges, however, must be the same size. A simple example where you know the range size is.... wksNew.Range("A1").Value = wksNew.Range("B1").Value -or if you are using entire columns then... wksNew.Columns("C:C").Value = wksNew.Columns("L:L").Value wksNew.Columns("L:L").ClearContents To make the destination range the same size as the origin range, you can use the "resize" property of the range object. Pick the top left cell of the destination range, say C5, then... dim newRange as Excel.Range Set newRange = wksNew.Range("C5").Resize(rngCurr.Rows.Count, rngCurr.Columns.Count) newRange.Value = rngCurr.Value rngCurr.ClearContents Note: When automating Excel, always set objects to Nothing before quitting the application, except for the application object which is set to nothing after quitting. Regards, Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "Jean" wrote in message Hi Jim I am trying to cut columns in range "rngCurr" to insert cut columns at column "C:C". I don't want to use Selection. But don't know any other way. Please help. Thanks Jean "Jim Cone" wrote in message Jean, Not quite sure what you are trying to do with all of the cuts and selects. Some general suggestions that should help... Don't select anything (with rare exceptions). Use range objects, do not use selection or activesheet or activecell ... Set rngToUse = wksNew.Columns(sColRange) If you don't use a range object then qualify it with the parent object ... wksNew.Columns("L:L") Remember that clearing a range will empty the clipboard. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Jean" wrote in message ... Hi, I am export some MS ACCESS 2003 Query records onto an Excel Spread Sheet. One section of the code is select a range of cells and move the these cells by the columns into another location. I am using "SELECTION". Please see below codes. The problem is I can only run the module (written in MS ACCESS) once without error. After the 1st time, I closed the Excel spreadsheet (genereated by the 1st run), call the module again, I would run into this error when it hit the "Selection" object. It would error at "Selection.Clear". But if I closed MS ACCESS and restart, I can run the report without error. 'Error: Object Variable Not Set or With Object Variable Not Set Set rngCurr = wksNew.Range(wksNew.Cells(6, 9), _ wksNew.Cells(7 + rstProjects.RecordCount, rstProjects.Fields.Count - 2)) 'wksNew.Columns(sColRange).Cut 'wksNew.Columns("I:L").Cut 'rngCurr.Columns.Select Selection.Clear rngCurr.Select Selection.EntireColumn.Select appExcel.CutCopyMode = False Selection.Cut 'ActiveWindow.LargeScroll ToRight:=-2 Columns("C:C").Select Selection.Insert Shift:=xlToRight Set rngCurr = Nothing Please help... Thanks Jean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Aling multiple sets of data by header column | Excel Discussion (Misc queries) | |||
SET statement tutorial | Excel Discussion (Misc queries) | |||
How do I include an all tab in a pivot variable selection | Excel Discussion (Misc queries) | |||
object variable or with block variable not set | Excel Discussion (Misc queries) | |||
Macro - Data Sort -Variable Selection | Excel Discussion (Misc queries) |