Hi All
I wish to pass a worksheet codename to function. How do I do this?

So far......this fails to pass the codename shAIF to the function

Sub Test
MsgBox FindReport(shAIF, "New Report")
End Sub

Function FindReport(searchSh as Worksheet, searchText as String) as Long
FindReport = 0
Dim c
With xSh
Set c = .Cells.Find(xFind, LookIn:=xlValues)
If Not c Is Nothing Then
FindReport = c.address.Row
End If
End With
End Function

--

Regards,
Nigel

Where or why do you need a sheet's code name. It's a bit confusing, you say
"this fails to pass the codename shAIF to the function"
but you havn't attempted to pass it, the first argument in your function
expects a worksheet object.

Sheet1, without quotes, could be passed if that's what you want, to refer to
a sheet within same project as the code (ie hardcoded and potentially
unreliable)
or are you saying you know the codename as a string and want to use that to
get a reference to the sheet object.

In passing, your function would work with following changes (subject to
receiving a worksheet object) -

change
With xSh
to
With searchSh

change
Set c = .Cells.Find(xFind, LookIn:=xlValues)
to
Set c = .Cells.Find(searchText , LookIn:=xlValues)

change
FindReport = c.address.Row
to
FindReport = c.Row

If you want to retrieve the codename from the worksheet object
Dim sCodeName as string

sCodeName = searchSh.Codename
NOTE cannot return codename of newly inserted sheet unless the VBE is open,
or the wb has been subsequently saved or quite a lot more work depending on
xl version.

Regards,
Peter T

"Nigel" wrote in message
...
Hi All
I wish to pass a worksheet codename to function. How do I do this?

So far......this fails to pass the codename shAIF to the function

Sub Test
MsgBox FindReport(shAIF, "New Report")
End Sub

Function FindReport(searchSh as Worksheet, searchText as String) as Long
FindReport = 0
Dim c
With xSh
Set c = .Cells.Find(xFind, LookIn:=xlValues)
If Not c Is Nothing Then
FindReport = c.address.Row
End If
End With
End Function

--

Regards,
Nigel

Must be one of those days <g...
Change With xSh to With searchSh
Change xFind to searchText
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Nigel"
wrote in message
Hi All
I wish to pass a worksheet codename to function. How do I do this?
So far......this fails to pass the codename shAIF to the function
Sub Test
MsgBox FindReport(shAIF, "New Report")
End Sub

Function FindReport(searchSh as Worksheet, searchText as String) as Long
FindReport = 0
Dim c
With xSh
Set c = .Cells.Find(xFind, LookIn:=xlValues)
If Not c Is Nothing Then
FindReport = c.address.Row
End If
End With
End Function
--
Regards,
Nigel

Yes it was, it now works as required. Cheers


--

Regards,
Nigel




"Jim Cone" wrote in message
...

Must be one of those days <g...
Change With xSh to With searchSh
Change xFind to searchText
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Nigel"
wrote in message
Hi All
I wish to pass a worksheet codename to function. How do I do this?
So far......this fails to pass the codename shAIF to the function
Sub Test
MsgBox FindReport(shAIF, "New Report")
End Sub

Function FindReport(searchSh as Worksheet, searchText as String) as Long
FindReport = 0
Dim c
With xSh
Set c = .Cells.Find(xFind, LookIn:=xlValues)
If Not c Is Nothing Then
FindReport = c.address.Row
End If
End With
End Function
--
Regards,
Nigel

Hi Peter

Thanks for the fixes to the code. Sorry to confuse but in addition

I have the codename which is shAIF, if I store the value shAIF in a string
(myString) and pass that it does not work?

So
FindReport(shAIF, searchText) ' works as shAIF resolves to a sheet

But
FindReport(myString, searchText) 'does not work!

Any help please.
--

Regards,
Nigel




"Peter T" <peter_t@discussions wrote in message
...
Where or why do you need a sheet's code name. It's a bit confusing, you
say
"this fails to pass the codename shAIF to the function"
but you havn't attempted to pass it, the first argument in your function
expects a worksheet object.

Sheet1, without quotes, could be passed if that's what you want, to refer
to
a sheet within same project as the code (ie hardcoded and potentially
unreliable)
or are you saying you know the codename as a string and want to use that
to
get a reference to the sheet object.

In passing, your function would work with following changes (subject to
receiving a worksheet object) -

change
With xSh
to
With searchSh

change
Set c = .Cells.Find(xFind, LookIn:=xlValues)
to
Set c = .Cells.Find(searchText , LookIn:=xlValues)

change
FindReport = c.address.Row
to
FindReport = c.Row

If you want to retrieve the codename from the worksheet object
Dim sCodeName as string

sCodeName = searchSh.Codename
NOTE cannot return codename of newly inserted sheet unless the VBE is
open,
or the wb has been subsequently saved or quite a lot more work depending
on
xl version.

Regards,
Peter T

"Nigel" wrote in message
...
Hi All
I wish to pass a worksheet codename to function. How do I do this?

So far......this fails to pass the codename shAIF to the function

Sub Test
MsgBox FindReport(shAIF, "New Report")
End Sub

Function FindReport(searchSh as Worksheet, searchText as String) as Long
FindReport = 0
Dim c
With xSh
Set c = .Cells.Find(xFind, LookIn:=xlValues)
If Not c Is Nothing Then
FindReport = c.address.Row
End If
End With
End Function

--

Regards,
Nigel

FindReport(shAIF, searchText) ' works as shAIF resolves to a sheet

Not sure what you mean by the "resolves" in "works as shAIF resolves to a
sheet". In order to work shAIF would need to be a sheet object or a
reference to a sheet object. If shAIF is the actual codename of a sheet, you
could pass it without quotes; note - as I mentioned before it would need to
be a sheet within the same project as the code, not necessarily the
activeworkbook.

FindReport(myString, searchText) 'does not work!

Obviously not, the first argument of FindReport expects a worksheet object,
not a string.

I'm still confused as to what you are trying to do.

Regards,
Peter T

"Nigel" wrote in message
...
Hi Peter

Thanks for the fixes to the code. Sorry to confuse but in addition

I have the codename which is shAIF, if I store the value shAIF in a string
(myString) and pass that it does not work?

So
FindReport(shAIF, searchText) ' works as shAIF resolves to a sheet

But
FindReport(myString, searchText) 'does not work!

Any help please.
--

Regards,
Nigel




"Peter T" <peter_t@discussions wrote in message
...
Where or why do you need a sheet's code name. It's a bit confusing, you
say
"this fails to pass the codename shAIF to the function"
but you havn't attempted to pass it, the first argument in your function
expects a worksheet object.

Sheet1, without quotes, could be passed if that's what you want, to
refer
to
a sheet within same project as the code (ie hardcoded and potentially
unreliable)
or are you saying you know the codename as a string and want to use that
to
get a reference to the sheet object.

In passing, your function would work with following changes (subject to
receiving a worksheet object) -

change
With xSh
to
With searchSh

change
Set c = .Cells.Find(xFind, LookIn:=xlValues)
to
Set c = .Cells.Find(searchText , LookIn:=xlValues)

change
FindReport = c.address.Row
to
FindReport = c.Row

If you want to retrieve the codename from the worksheet object
Dim sCodeName as string

sCodeName = searchSh.Codename
NOTE cannot return codename of newly inserted sheet unless the VBE is
open,
or the wb has been subsequently saved or quite a lot more work depending
on
xl version.

Regards,
Peter T

"Nigel" wrote in message
...
Hi All
I wish to pass a worksheet codename to function. How do I do this?

So far......this fails to pass the codename shAIF to the function

Sub Test
MsgBox FindReport(shAIF, "New Report")
End Sub

Function FindReport(searchSh as Worksheet, searchText as String) as
Long
FindReport = 0
Dim c
With xSh
Set c = .Cells.Find(xFind, LookIn:=xlValues)
If Not c Is Nothing Then
FindReport = c.address.Row
End If
End With
End Function

--

Regards,
Nigel