Object variable not set

[monagan]

I have this code that opens a another workbook and selects the worksheet.

Sheet one has the Employee Name, The Date and The Worksheets to Open and the hours worked



Sub openTimeSheet()
Dim OpenSheet As String
Dim wbTS As Workbook
Dim lRow As Long
Dim iCol As Integer
Dim NAME As String

NAME = Range("a14", "a14").Text

OpenSheet = Sheets("sheet1").Cells(17, 4).Value

Set wbTS = Workbooks.Open("C:\Documents and Settings\AMY C\Desktop\TIME SHEETS.xls")
wbTS.Sheets(OpenSheet).Select

lRow = Columns(1).Cells.Find(NAME, LookIn:=xlValues).Row
iCol = Rows(1).Cells.Find("h7", LookIn:=xlValues).Column
Cells(lRow, iCol).Select
End Sub

It opens everything fine

but stops at

lRow = Columns(1).Cells.Find(NAME, LookIn:=xlValues).Row

Anyone have any ideas on where I'm going wrong?

 

[Molby]

'Name' is a reserved word, try changing it to EName or something like that.

 

[Chance1234]

lRow = Columns(1).Cells.Find(NAME, LookIn:=xlValues).Row

you need to reference the sheet and the workbook

either

Dim xl_sheet as sheet
xl_sheet = wbTS.Sheets(OpenSheet).Select

xl_sheet.Columns(1).Cells.Find(NAME, LookIn:=xlValues).Row

or wbts.sheets(Opensheet).Columns(1).Cells.Find(NAME, LookIn:=xlValues).Row


or OpensheetColumns(1).Cells.Find(NAME, LookIn:=xlValues).Row
seeing that you already have the reference


Chance,

Filmmaker, gentlemen and Cavalier

http://WWW.5YLAC.S5.COM

,

http://WWW.HOWTOBESPOOKY.CO.UK,

http://WWW.HENRYV.CO.UK

 

[monagan]

That didn't work either. I still get the same error, ENAME is getting the value from the other worksheet though

 

[PHV]

You may replace this:
lRow = Columns(1).Cells.Find(NAME, LookIn:=xlValues).Row
By this:
Dim f
Set f = Columns(1).Find(NAME, LookIn:=xlValues)
If f Is Nothing Then
MsgBox NAME & " NOT FOUND"
Else
lRow = f.Row
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[monagan]

Chance that worked great.

Thank you so much.

Now it is telling me I am using

Cells(lRow, iCol).Select

Wrong.

The wrong way to use the select method.

 

[Chance1234]

exactly the same as lrow, you need to reference the sheet first.

the computer needs to know exactly where to do things,



Chance,

Filmmaker, gentlemen and Cavalier

http://WWW.5YLAC.S5.COM

,

http://WWW.HOWTOBESPOOKY.CO.UK,

http://WWW.HENRYV.CO.UK

 

[monagan]

I have the code above, and everythign works fine.

I need a statement that puts the value in EHOURS
in the cell that is selected.

I have tried
wbTS.Sheets(OpenSheet).Cells(lRow, iCol).Select.Text = EHOURS

wbTS.Sheets(OpenSheet).Cells(lRow, iCol).Select = EHOURS

and so on but nothing seems to be working

 

[PHV]

And this ?
wbTS.Sheets(OpenSheet).Cells(lRow, iCol).Value = EHOURS


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[monagan]

Thanks PHV,

What I endud up doing is this

wbTS.Sheets(OpenSheet).Cells(lRow, iCol).Select
ActiveCell.Value = EHOURS

 

[Chance1234]

you dont need the .select and the activecell malarkey, PHV line should work just grand.



Chance,

Filmmaker, gentlemen and Cavalier

http://WWW.5YLAC.S5.COM

,

http://WWW.HOWTOBESPOOKY.CO.UK,

http://WWW.HENRYV.CO.UK