Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ERROR 424" OBJECT REQUIRED
The scripts below is working but
is creating a new master "sheet". No there is existing sheet name "WPS Detail Dates" which i want the information to appear.I want skip or exclude rows A and F "rows" (N/A). I don't want the scripts to affect them" They showing N/A" I want to free those rows. NOTE. I tried to edit it not to create new master sheet and insert the information in sheet "WPS Detail Dates" but it showing ERROR " RUN-TIME ERROR 424" OBJECT REQUIRED. So please help me look into it. Thanks alot. Below is the code: Sub CreateMaster() Set Source = Workbooks("Source.xls") Set SourceSht = Source.Sheets("Sheet1") 'Set SourceSht = Sheets("Sheet1") Set Dest = Workbooks("Destination.xls") 'Set Dest = ThisWorkbook 'create new worksheet With Dest Set DestSht = .Sheets.Add(after:=.Sheets(.Sheets.Count)) DestSht.Name = "Master" End With With DestSht SourceSht.Columns("C:C").Copy _ Destination:=.Columns("C:C") Lastrow = .Range("C" & Rows.Count).End(xlUp).Row .Range("C1:C" & Lastrow).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=.Range("B1"), _ Unique:=True 'delete temporay column C .Columns("C").Delete .Range("A1") = "SALES" .Range("B1") = "ID" .Range("C1") = "Employee" .Range("D1") = "Hire Date" .Range("E1") = "Manager" .Range("F1") = "Reg" .Range("G1") = "Title" Lastrow = .Range("B" & Rows.Count).End(xlUp).Row For RowCount = 2 To Lastrow ID = .Range("B" & RowCount) With SourceSht Set c = .Columns("C").Find(what:=ID, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find ID : " & ID) Else Sales = "N/A" Reg = "N/A" Employee = .Range("A" & c.Row) HireDate = .Range("D" & c.Row) Title = .Range("E" & c.Row) Manager = .Range("G" & c.Row) End If End With If Not c Is Nothing Then .Range("A" & RowCount) = Sales .Range("C" & RowCount) = Employee .Range("D" & RowCount) = HireDate .Range("E" & RowCount) = Manager .Range("F" & RowCount) = Reg .Range("G" & RowCount) = Title Else MsgBox ("Error : Count not find ID : " & ID) End If Next RowCount End With End Sub Sub UpdateMaster() Set Source = Workbooks("Source.xls") Set SourceSht = Source.Sheets("Sheet1") 'Set SourceSht = Sheets("Sheet1") Set Dest = Workbooks("Destination.xls") 'Set Dest = ThisWorkbook Set DestSht = Dest.Sheets("Master") With DestSht Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 End With With SourceSht Lastrow = .Range("C" & Rows.Count).End(xlUp).Row For RowCount = 2 To Lastrow Sales = "N/A" Reg = "N/A" ID = .Range("C" & RowCount) Employee = .Range("A" & RowCount) HireDate = .Range("D" & RowCount) Title = .Range("E" & RowCount) Manager = .Range("G" & RowCount) With DestSht Set c = .Columns("B").Find(what:=ID, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then DataRow = NewRow NewRow = NewRow + 1 .Range("B" & DataRow) = ID Else DataRow = c.Row End If .Range("A" & DataRow) = Sales .Range("C" & DataRow) = Employee .Range("D" & DataRow) = HireDate .Range("E" & DataRow) = Manager .Range("F" & DataRow) = Reg .Range("G" & DataRow) = Title End With Next RowCount End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ERROR 424" OBJECT REQUIRED
Which line generates the error? -- HTH... Jim Thomlinson "Kanmi" wrote: The scripts below is working but is creating a new master "sheet". No there is existing sheet name "WPS Detail Dates" which i want the information to appear.I want skip or exclude rows A and F "rows" (N/A). I don't want the scripts to affect them" They showing N/A" I want to free those rows. NOTE. I tried to edit it not to create new master sheet and insert the information in sheet "WPS Detail Dates" but it showing ERROR " RUN-TIME ERROR 424" OBJECT REQUIRED. So please help me look into it. Thanks alot. Below is the code: Sub CreateMaster() Set Source = Workbooks("Source.xls") Set SourceSht = Source.Sheets("Sheet1") 'Set SourceSht = Sheets("Sheet1") Set Dest = Workbooks("Destination.xls") 'Set Dest = ThisWorkbook 'create new worksheet With Dest Set DestSht = .Sheets.Add(after:=.Sheets(.Sheets.Count)) DestSht.Name = "Master" End With With DestSht SourceSht.Columns("C:C").Copy _ Destination:=.Columns("C:C") Lastrow = .Range("C" & Rows.Count).End(xlUp).Row .Range("C1:C" & Lastrow).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=.Range("B1"), _ Unique:=True 'delete temporay column C .Columns("C").Delete .Range("A1") = "SALES" .Range("B1") = "ID" .Range("C1") = "Employee" .Range("D1") = "Hire Date" .Range("E1") = "Manager" .Range("F1") = "Reg" .Range("G1") = "Title" Lastrow = .Range("B" & Rows.Count).End(xlUp).Row For RowCount = 2 To Lastrow ID = .Range("B" & RowCount) With SourceSht Set c = .Columns("C").Find(what:=ID, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find ID : " & ID) Else Sales = "N/A" Reg = "N/A" Employee = .Range("A" & c.Row) HireDate = .Range("D" & c.Row) Title = .Range("E" & c.Row) Manager = .Range("G" & c.Row) End If End With If Not c Is Nothing Then .Range("A" & RowCount) = Sales .Range("C" & RowCount) = Employee .Range("D" & RowCount) = HireDate .Range("E" & RowCount) = Manager .Range("F" & RowCount) = Reg .Range("G" & RowCount) = Title Else MsgBox ("Error : Count not find ID : " & ID) End If Next RowCount End With End Sub Sub UpdateMaster() Set Source = Workbooks("Source.xls") Set SourceSht = Source.Sheets("Sheet1") 'Set SourceSht = Sheets("Sheet1") Set Dest = Workbooks("Destination.xls") 'Set Dest = ThisWorkbook Set DestSht = Dest.Sheets("Master") With DestSht Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 End With With SourceSht Lastrow = .Range("C" & Rows.Count).End(xlUp).Row For RowCount = 2 To Lastrow Sales = "N/A" Reg = "N/A" ID = .Range("C" & RowCount) Employee = .Range("A" & RowCount) HireDate = .Range("D" & RowCount) Title = .Range("E" & RowCount) Manager = .Range("G" & RowCount) With DestSht Set c = .Columns("B").Find(what:=ID, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then DataRow = NewRow NewRow = NewRow + 1 .Range("B" & DataRow) = ID Else DataRow = c.Row End If .Range("A" & DataRow) = Sales .Range("C" & DataRow) = Employee .Range("D" & DataRow) = HireDate .Range("E" & DataRow) = Manager .Range("F" & DataRow) = Reg .Range("G" & DataRow) = Title End With Next RowCount End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Object required" error | Excel Programming | |||
"Object Required" error when calling .Net DLL | Excel Programming | |||
"Microsoft Visual Basic runtime error '424' object required". | Excel Worksheet Functions | |||
error while compiling "OBJECT REQUIRED" | Excel Discussion (Misc queries) | |||
Getting "Object Required" Error Message - Need Help !!! | Excel Programming |