Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Required error on Weekly Summary sheet creation
I'm removing dupes from a list. The following code gives an "object
required" error on the marked line. The variable is declared as a range object, is set, and is recognized in the commands immediately above. So why not in this line? Sub WklySumWS() Dim wbWkly As Workbook Dim wsWklyLst As Worksheet Dim wsWklySum As Worksheet Dim sWklyShtName As String Dim rStartCell As Range Dim rTaskList As Range Dim rTaskStart As Range Dim rTask As Range Dim rNextTask As Range Dim sTask As String Dim sNextTask As String Dim lTask As Long lTask = 1 Set wsWklyLst = ActiveSheet Set rStartCell = wsWklyLst.Range("B1") sWklyShtName = wsWklyLst.Name 'Create Weekly Summary Sheet Sheets.Add.Activate Set wsWklySum = ActiveSheet Set rTaskStart = wsWklySum.Range("A1") ActiveSheet.Name = sWklyShtName & " Summary" wsWklySum.Move Befo=Sheets("Project Summary") wsWklyLst.Range(rStartCell, rStartCell.End(xlDown)).Copy wsWklySum.Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.Sort Key1:=Range("A1"), Order1:=xlAscending Set rTaskList = wsWklySum.Range(rTaskStart, rTaskStart.End(xlDown)) For Each rTask In rTaskList sTask = rTask.Value Set rNextTask = rTask.Offset(1, 0) sNextTask = rNextTask Do While sNextTask < "" Do While sTask = sNextTask rNextTask.EntireRow.Delete <----OBJECT REQUIRED ERROR Loop Loop Next rTask End Sub Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Required error on Weekly Summary sheet creation
It appears that you haven't declared your variables consistently. Put Option
Explicit before your sub and then compile the VBA project to figure out the problems. -- HTH, Barb Reinhardt "davegb" wrote: I'm removing dupes from a list. The following code gives an "object required" error on the marked line. The variable is declared as a range object, is set, and is recognized in the commands immediately above. So why not in this line? Sub WklySumWS() Dim wbWkly As Workbook Dim wsWklyLst As Worksheet Dim wsWklySum As Worksheet Dim sWklyShtName As String Dim rStartCell As Range Dim rTaskList As Range Dim rTaskStart As Range Dim rTask As Range Dim rNextTask As Range Dim sTask As String Dim sNextTask As String Dim lTask As Long lTask = 1 Set wsWklyLst = ActiveSheet Set rStartCell = wsWklyLst.Range("B1") sWklyShtName = wsWklyLst.Name 'Create Weekly Summary Sheet Sheets.Add.Activate Set wsWklySum = ActiveSheet Set rTaskStart = wsWklySum.Range("A1") ActiveSheet.Name = sWklyShtName & " Summary" wsWklySum.Move Befo=Sheets("Project Summary") wsWklyLst.Range(rStartCell, rStartCell.End(xlDown)).Copy wsWklySum.Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.Sort Key1:=Range("A1"), Order1:=xlAscending Set rTaskList = wsWklySum.Range(rTaskStart, rTaskStart.End(xlDown)) For Each rTask In rTaskList sTask = rTask.Value Set rNextTask = rTask.Offset(1, 0) sNextTask = rNextTask Do While sNextTask < "" Do While sTask = sNextTask rNextTask.EntireRow.Delete <----OBJECT REQUIRED ERROR Loop Loop Next rTask End Sub Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Required error on Weekly Summary sheet creation
Oops, I think I goofed. :) What I'd recommend is defining a range to
delete while you go through your loop and once you are finished with the whole range, delete the rows in question. Something like this Set DeleteRange = Nothing In the For next loop add ranges to delete if DeleteRange is nothing then Set DeleteRange = rNextTask else Set DeleteRange = union (DeleteRange,rNextTask) end if Once you are done with the For Next do this DeleteRange.entirerow.delete -- HTH, Barb Reinhardt "davegb" wrote: I'm removing dupes from a list. The following code gives an "object required" error on the marked line. The variable is declared as a range object, is set, and is recognized in the commands immediately above. So why not in this line? Sub WklySumWS() Dim wbWkly As Workbook Dim wsWklyLst As Worksheet Dim wsWklySum As Worksheet Dim sWklyShtName As String Dim rStartCell As Range Dim rTaskList As Range Dim rTaskStart As Range Dim rTask As Range Dim rNextTask As Range Dim sTask As String Dim sNextTask As String Dim lTask As Long lTask = 1 Set wsWklyLst = ActiveSheet Set rStartCell = wsWklyLst.Range("B1") sWklyShtName = wsWklyLst.Name 'Create Weekly Summary Sheet Sheets.Add.Activate Set wsWklySum = ActiveSheet Set rTaskStart = wsWklySum.Range("A1") ActiveSheet.Name = sWklyShtName & " Summary" wsWklySum.Move Befo=Sheets("Project Summary") wsWklyLst.Range(rStartCell, rStartCell.End(xlDown)).Copy wsWklySum.Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.Sort Key1:=Range("A1"), Order1:=xlAscending Set rTaskList = wsWklySum.Range(rTaskStart, rTaskStart.End(xlDown)) For Each rTask In rTaskList sTask = rTask.Value Set rNextTask = rTask.Offset(1, 0) sNextTask = rNextTask Do While sNextTask < "" Do While sTask = sNextTask rNextTask.EntireRow.Delete <----OBJECT REQUIRED ERROR Loop Loop Next rTask End Sub Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Required error on Weekly Summary sheet creation
On Mar 20, 9:48*am, Barb Reinhardt
wrote: It appears that you haven't declared your variables consistently. *Put Option Explicit before your sub and then compile the VBA project to figure out the problems. -- HTH, Barb Reinhardt "davegb" wrote: I'm removing dupes from a list. The following code gives an "object required" error on the marked line. The variable is declared as a range object, is set, and is recognized in the commands immediately above. So why not in this line? Sub WklySumWS() Dim wbWkly As Workbook Dim wsWklyLst As Worksheet Dim wsWklySum As Worksheet Dim sWklyShtName As String Dim rStartCell As Range Dim rTaskList As Range Dim rTaskStart As Range Dim rTask As Range Dim rNextTask As Range Dim sTask As String Dim sNextTask As String Dim lTask As Long lTask = 1 Set wsWklyLst = ActiveSheet Set rStartCell = wsWklyLst.Range("B1") sWklyShtName = wsWklyLst.Name 'Create Weekly Summary Sheet Sheets.Add.Activate Set wsWklySum = ActiveSheet Set rTaskStart = wsWklySum.Range("A1") ActiveSheet.Name = sWklyShtName & " Summary" wsWklySum.Move Befo=Sheets("Project Summary") wsWklyLst.Range(rStartCell, rStartCell.End(xlDown)).Copy wsWklySum.Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ * * * False, Transpose:=False Selection.Sort Key1:=Range("A1"), Order1:=xlAscending Set rTaskList = wsWklySum.Range(rTaskStart, rTaskStart.End(xlDown)) For Each rTask In rTaskList * *sTask = rTask.Value * *Set rNextTask = rTask.Offset(1, 0) * *sNextTask = rNextTask * *Do While sNextTask < "" * * * *Do While sTask = sNextTask * * * * * *rNextTask.EntireRow.Delete <----OBJECT REQUIRED ERROR * * * *Loop * *Loop Next rTask End Sub Thanks in advance.- Hide quoted text - - Show quoted text - Thanks for the reply, but I've already done all that, as stated in my original post. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Required error on Weekly Summary sheet creation
On Mar 20, 9:53*am, Barb Reinhardt
wrote: Oops, I think I goofed. *:) * What I'd recommend is defining a range to delete while you go through your loop and once you are finished with the whole range, delete the rows in question. * Something like this Set DeleteRange = Nothing In the For next loop add ranges to delete if DeleteRange is nothing then * * Set DeleteRange = rNextTask else * * Set DeleteRange = union (DeleteRange,rNextTask) end if Once you are done with the For Next do this DeleteRange.entirerow.delete -- HTH, Barb Reinhardt "davegb" wrote: I'm removing dupes from a list. The following code gives an "object required" error on the marked line. The variable is declared as a range object, is set, and is recognized in the commands immediately above. So why not in this line? Sub WklySumWS() Dim wbWkly As Workbook Dim wsWklyLst As Worksheet Dim wsWklySum As Worksheet Dim sWklyShtName As String Dim rStartCell As Range Dim rTaskList As Range Dim rTaskStart As Range Dim rTask As Range Dim rNextTask As Range Dim sTask As String Dim sNextTask As String Dim lTask As Long lTask = 1 Set wsWklyLst = ActiveSheet Set rStartCell = wsWklyLst.Range("B1") sWklyShtName = wsWklyLst.Name 'Create Weekly Summary Sheet Sheets.Add.Activate Set wsWklySum = ActiveSheet Set rTaskStart = wsWklySum.Range("A1") ActiveSheet.Name = sWklyShtName & " Summary" wsWklySum.Move Befo=Sheets("Project Summary") wsWklyLst.Range(rStartCell, rStartCell.End(xlDown)).Copy wsWklySum.Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ * * * False, Transpose:=False Selection.Sort Key1:=Range("A1"), Order1:=xlAscending Set rTaskList = wsWklySum.Range(rTaskStart, rTaskStart.End(xlDown)) For Each rTask In rTaskList * *sTask = rTask.Value * *Set rNextTask = rTask.Offset(1, 0) * *sNextTask = rNextTask * *Do While sNextTask < "" * * * *Do While sTask = sNextTask * * * * * *rNextTask.EntireRow.Delete <----OBJECT REQUIRED ERROR * * * *Loop * *Loop Next rTask End Sub Thanks in advance.- Hide quoted text - - Show quoted text - Thanks for the suggestions. I'm still trying to figure out why that original code fails, so I can learn from my mistakes. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weekly Time Sheet Summary | Excel Worksheet Functions | |||
Weekly Worksheets from others merged into one summary sheet | Excel Worksheet Functions | |||
Generating a dynamic monthly summary sheet based on weekly data sh | Excel Discussion (Misc queries) | |||
Object required error | Excel Programming | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |