Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error 424 Object Required
Hi, I don't understand why I'm getting this error. I have declared all th variables and I have tried using and not using the Set statement. Coul use some help. The error is: Run Time error 424 Object Required Here is the Code: I've commented out the error handling to find th error. Option Explicit Sub CreateRoomSheets() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range, rngNR As Range, rng1 As Range, rng2 As Range Dim rng3 As Range, rng4 As Range, rng5 As Range, rCell As Range Set WB = ThisWorkbook Set SH = WB.Sheets("Room Blank") Set rng = WB.Sheets("Room List").Range("RoomNo") Set rngNR = WB.Sheets("RoomTypes").Range("ItemTable") Set rng1 = WB.Sheets("Room List").Range("RoomName") Set rng2 = WB.Sheets("Room List").Range("RoomTypeCol") Set rng3 = WB.Sheets("Room List").Range("RoomRefCol") 'On Error GoTo RET Application.ScreenUpdating = False For Each rCell In rng.Cells Set rng4 = Excel.WorksheetFunction.HLookup(rCell.Offset(0 3).Value, rngNR, 1, False) '<====ERROR Set rng5 = Range(rng4.Offset(1, 0), rng4.Offset(51, 1)) rng5.Copy With rCell SH.Copy After:=WB.Sheets(WB.Sheets.Count) With ActiveSheet .Name = rCell.Value .Range("B2") = rCell.Value .Range("B3") = rCell.Offset(0, 1).Value .Range("B4") = rCell.Offset(0, 2).Value .Range("A6").PasteSpecial Paste:=xlPasteValues Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End With End With Next rCell 'RET: Application.ScreenUpdating = True End Su -- Case ----------------------------------------------------------------------- Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454 View this thread: http://www.excelforum.com/showthread.php?threadid=53233 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error 424 Object Required
Hlookup returns a value not a range object. You would probably be better off
using a find function. set rng4 = rngNR.Find(What:=rCell.Offset(0,3).Value, _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=False) if rng4 is nothing then msgbox "Sorry, The item was not found" else 'continue on with your code... -- HTH... Jim Thomlinson "Casey" wrote: Hi, I don't understand why I'm getting this error. I have declared all the variables and I have tried using and not using the Set statement. Could use some help. The error is: Run Time error 424 Object Required Here is the Code: I've commented out the error handling to find the error. Option Explicit Sub CreateRoomSheets() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range, rngNR As Range, rng1 As Range, rng2 As Range Dim rng3 As Range, rng4 As Range, rng5 As Range, rCell As Range Set WB = ThisWorkbook Set SH = WB.Sheets("Room Blank") Set rng = WB.Sheets("Room List").Range("RoomNo") Set rngNR = WB.Sheets("RoomTypes").Range("ItemTable") Set rng1 = WB.Sheets("Room List").Range("RoomName") Set rng2 = WB.Sheets("Room List").Range("RoomTypeCol") Set rng3 = WB.Sheets("Room List").Range("RoomRefCol") 'On Error GoTo RET Application.ScreenUpdating = False For Each rCell In rng.Cells Set rng4 = Excel.WorksheetFunction.HLookup(rCell.Offset(0, 3).Value, rngNR, 1, False) '<====ERROR Set rng5 = Range(rng4.Offset(1, 0), rng4.Offset(51, 1)) rng5.Copy With rCell SH.Copy After:=WB.Sheets(WB.Sheets.Count) With ActiveSheet .Name = rCell.Value .Range("B2") = rCell.Value .Range("B3") = rCell.Offset(0, 1).Value .Range("B4") = rCell.Offset(0, 2).Value .Range("A6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End With End With Next rCell 'RET: Application.ScreenUpdating = True End Sub -- Casey ------------------------------------------------------------------------ Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=532338 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error 424 Object Required
Hlookup returns a value, not a range object.
Match will return an offset into the range. You can use that to pick up the cell if rngNr is a single row like set rngNr = rows(6) Dim res as Variant res = Application.Match(rCell.Offset(0,3).Value, rngNR,0) if not iserror(res) then set rng4 = rngNr(1,res) -- Regards, Tom Ogilvy "Casey" wrote: Hi, I don't understand why I'm getting this error. I have declared all the variables and I have tried using and not using the Set statement. Could use some help. The error is: Run Time error 424 Object Required Here is the Code: I've commented out the error handling to find the error. Option Explicit Sub CreateRoomSheets() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range, rngNR As Range, rng1 As Range, rng2 As Range Dim rng3 As Range, rng4 As Range, rng5 As Range, rCell As Range Set WB = ThisWorkbook Set SH = WB.Sheets("Room Blank") Set rng = WB.Sheets("Room List").Range("RoomNo") Set rngNR = WB.Sheets("RoomTypes").Range("ItemTable") Set rng1 = WB.Sheets("Room List").Range("RoomName") Set rng2 = WB.Sheets("Room List").Range("RoomTypeCol") Set rng3 = WB.Sheets("Room List").Range("RoomRefCol") 'On Error GoTo RET Application.ScreenUpdating = False For Each rCell In rng.Cells Set rng4 = Excel.WorksheetFunction.HLookup(rCell.Offset(0, 3).Value, rngNR, 1, False) '<====ERROR Set rng5 = Range(rng4.Offset(1, 0), rng4.Offset(51, 1)) rng5.Copy With rCell SH.Copy After:=WB.Sheets(WB.Sheets.Count) With ActiveSheet .Name = rCell.Value .Range("B2") = rCell.Value .Range("B3") = rCell.Offset(0, 1).Value .Range("B4") = rCell.Offset(0, 2).Value .Range("A6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End With End With Next rCell 'RET: Application.ScreenUpdating = True End Sub -- Casey ------------------------------------------------------------------------ Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=532338 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error 424 Object Required
Jim, Thank you. I got an additional 1004 error when the code went to paste the rng5 information into A6 of the ActiveSheet, so I moved the rng5.copy line and everthing worked great. Thank you for the lesson on the HLookup returning a value and not a range. That made it click for me. For the benefit of others the corrected Code: Option Explicit Sub CreateRoomSheets() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range, rngNR As Range, rng1 As Range, rng2 As Range Dim rng3 As Range, rng4 As Range, rng5 As Range, rCell As Range Set WB = ThisWorkbook Set SH = WB.Sheets("Room Blank") Set rng = WB.Sheets("Room List").Range("RoomNo") Set rngNR = WB.Sheets("RoomTypes").Range("ItemTable") Set rng1 = WB.Sheets("Room List").Range("RoomName") Set rng2 = WB.Sheets("Room List").Range("RoomTypeCol") Set rng3 = WB.Sheets("Room List").Range("RoomRefCol") On Error GoTo RET Application.ScreenUpdating = False For Each rCell In rng.Cells 'Excel.WorksheetFunction Set rng4 = rngNR.Find(What:=rCell.Offset(0, 3).Value, _ LookAt:=xlWhole, LookIn:=xlFormulas, MatchCase:=False) If rng4 Is Nothing Then MsgBox "Sorry, The item was not found" End If Set rng5 = Range(rng4.Offset(1, 0), rng4.Offset(51, 1)) rng5.Copy With rCell SH.Copy After:=WB.Sheets(WB.Sheets.Count) With ActiveSheet ..Name = rCell.Value ..Range("B2") = rCell.Value ..Range("B3") = rCell.Offset(0, 1).Value ..Range("B4") = rCell.Offset(0, 2).Value rng5.Copy ..Range("A6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End With End With Next rCell RET: Application.ScreenUpdating = True End Sub -- Casey ------------------------------------------------------------------------ Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=532338 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error 424 Object Required
Tom, Thank you for your reply. I always appreciate the conciseness (hope that's a real word) of your code. I got a great bit of code from you today in an answer you gave to another OP, regarding adding up a filtered list. I have needed to do that on a number of worksheets and knew the built-in Subtotal function would do it but never could find an example of the Autofilter and Subtotal used together to get there. Great code. -- Casey ------------------------------------------------------------------------ Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=532338 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error 424 Object Required
If rng4 is not set then the rest of the code will bomb out. You might want to
add an exit if the item is not found... If rng4 Is Nothing Then MsgBox "Sorry, The item was not found" exit sub End If -- HTH... Jim Thomlinson "Casey" wrote: Jim, Thank you. I got an additional 1004 error when the code went to paste the rng5 information into A6 of the ActiveSheet, so I moved the rng5.copy line and everthing worked great. Thank you for the lesson on the HLookup returning a value and not a range. That made it click for me. For the benefit of others the corrected Code: Option Explicit Sub CreateRoomSheets() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range, rngNR As Range, rng1 As Range, rng2 As Range Dim rng3 As Range, rng4 As Range, rng5 As Range, rCell As Range Set WB = ThisWorkbook Set SH = WB.Sheets("Room Blank") Set rng = WB.Sheets("Room List").Range("RoomNo") Set rngNR = WB.Sheets("RoomTypes").Range("ItemTable") Set rng1 = WB.Sheets("Room List").Range("RoomName") Set rng2 = WB.Sheets("Room List").Range("RoomTypeCol") Set rng3 = WB.Sheets("Room List").Range("RoomRefCol") On Error GoTo RET Application.ScreenUpdating = False For Each rCell In rng.Cells 'Excel.WorksheetFunction Set rng4 = rngNR.Find(What:=rCell.Offset(0, 3).Value, _ LookAt:=xlWhole, LookIn:=xlFormulas, MatchCase:=False) If rng4 Is Nothing Then MsgBox "Sorry, The item was not found" End If Set rng5 = Range(rng4.Offset(1, 0), rng4.Offset(51, 1)) rng5.Copy With rCell SH.Copy After:=WB.Sheets(WB.Sheets.Count) With ActiveSheet .Name = rCell.Value .Range("B2") = rCell.Value .Range("B3") = rCell.Offset(0, 1).Value .Range("B4") = rCell.Offset(0, 2).Value rng5.Copy .Range("A6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End With End With Next rCell RET: Application.ScreenUpdating = True End Sub -- Casey ------------------------------------------------------------------------ Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=532338 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error 424 Object Required
Jim, Thanks. I not very good at anticipating errors and coming up with ways to handle them yet. I often overlook that stuff. Question; since I have an Error handler that goes to RET, do I still need the extra? And if I do, should I exit to RET to reset ScreenUpdating? Something like: If rng4 Is Nothing Then MsgBox "Sorry, The item was not found" GoTo RET 'instead of End Sub????? End If -- Casey ------------------------------------------------------------------------ Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=532338 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error 424 Object Required
With the exception of the error handler I avoid using goto's as they can
become a real nightmare to debug. If it was me I would do something like this with an if else statement. It skips all of the code if rng4 is nothing and goes right to the end... That being said a goto will work. Option Explicit Sub CreateRoomSheets() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range, rngNR As Range, rng1 As Range, rng2 As Range Dim rng3 As Range, rng4 As Range, rng5 As Range, rCell As Range Set WB = ThisWorkbook Set SH = WB.Sheets("Room Blank") Set rng = WB.Sheets("Room List").Range("RoomNo") Set rngNR = WB.Sheets("RoomTypes").Range("ItemTable") Set rng1 = WB.Sheets("Room List").Range("RoomName") Set rng2 = WB.Sheets("Room List").Range("RoomTypeCol") Set rng3 = WB.Sheets("Room List").Range("RoomRefCol") On Error GoTo RET Application.ScreenUpdating = False For Each rCell In rng.Cells 'Excel.WorksheetFunction Set rng4 = rngNR.Find(What:=rCell.Offset(0, 3).Value, _ LookAt:=xlWhole, LookIn:=xlFormulas, MatchCase:=False) If rng4 Is Nothing Then MsgBox "Sorry, The item was not found" Else Set rng5 = Range(rng4.Offset(1, 0), rng4.Offset(51, 1)) rng5.Copy With rCell SH.Copy After:=WB.Sheets(WB.Sheets.Count) With ActiveSheet .Name = rCell.Value .Range("B2") = rCell.Value .Range("B3") = rCell.Offset(0, 1).Value .Range("B4") = rCell.Offset(0, 2).Value rng5.Copy .Range("A6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End With End With Next rCell end if RET: Application.ScreenUpdating = True End Sub -- HTH... Jim Thomlinson "Casey" wrote: Jim, Thanks. I not very good at anticipating errors and coming up with ways to handle them yet. I often overlook that stuff. Question; since I have an Error handler that goes to RET, do I still need the extra? And if I do, should I exit to RET to reset ScreenUpdating? Something like: If rng4 Is Nothing Then MsgBox "Sorry, The item was not found" GoTo RET 'instead of End Sub????? End If -- Casey ------------------------------------------------------------------------ Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=532338 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time error 424 Object Required
Jim, I like the else statement. I will go that route. Thanks again for all the help and advice. -- Casey ------------------------------------------------------------------------ Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=532338 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-Time Error 424 - Object Required | Excel Programming | |||
Object required - run time error 424. | Excel Programming | |||
Run-time error '424': Object required | Excel Programming | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming | |||
Dget in VBA gets Object required run-time error? | Excel Programming |