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