find worksheet by FindMethod
 

Good Day Group,

Trying to use the FindMethod to lookup if a Worksheet is present.
An effort to code as below. Seems I need further advise. Thankkful
for any help.

Brgds

CG Rosén


Dim nme As String
nme = TextBox1.Text & TextBox2.Text
Dim wk As Worksheets

Dim k
With ActiveWorkbook.Worksheets
Set k = .Find(????, LookIn:=????)

If Not k Is Nothing Then

code.....

End If
End With

find worksheet by FindMethod
 

Don't use FInd, it' a wrong tool for the task

I would do this:

Function IsPresent (ws_name as string, wb_name as string) as boolean
Dim ws as worksheet
Dim wb as Workbook

set wb=application.workbooks(wb_name)

IsPresent = false ' assume there is no such ws in this wb

for each ws in wb
if ws.name = ws_name then
isPresent=True
exit function
end if
next ws
end function

To use this function, just type

if IsPresent("MySheet_name", "MyBook_name") then ....

Cheers,

RADO




"CG Rosén" wrote in message
...
Good Day Group,

Trying to use the FindMethod to lookup if a Worksheet is present.
An effort to code as below. Seems I need further advise. Thankkful
for any help.

Brgds

CG Rosén


Dim nme As String
nme = TextBox1.Text & TextBox2.Text
Dim wk As Worksheets

Dim k
With ActiveWorkbook.Worksheets
Set k = .Find(????, LookIn:=????)

If Not k Is Nothing Then

code.....

End If
End With

find worksheet by FindMethod
 

Dim nme As String
nme = TextBox1.Text & TextBox2.Text
Dim wk As Worksheet

On Error Resume Next
set wk = Worksheets(nme")
On Error Goto 0

if not wk is Nothing then
' worksheet exists (wk holds a reference to it)

--
Regards,
Tom Ogilvy



"CG Rosén" wrote in message
...
Good Day Group,

Trying to use the FindMethod to lookup if a Worksheet is present.
An effort to code as below. Seems I need further advise. Thankkful
for any help.

Brgds

CG Rosén


Dim nme As String
nme = TextBox1.Text & TextBox2.Text
Dim wk As Worksheets

Dim k
With ActiveWorkbook.Worksheets
Set k = .Find(????, LookIn:=????)

If Not k Is Nothing Then

code.....

End If
End With

find worksheet by FindMethod
 

set wk = Worksheets(nme")
shouldn't have the doublequote

set wk = Worksheets(nme)

--
Regards,
Tom Ogilvy

Tom Ogilvy wrote in message
...
Dim nme As String
nme = TextBox1.Text & TextBox2.Text
Dim wk As Worksheet

On Error Resume Next
set wk = Worksheets(nme")
On Error Goto 0

if not wk is Nothing then
' worksheet exists (wk holds a reference to it)

--
Regards,
Tom Ogilvy



"CG Rosén" wrote in message
...
Good Day Group,

Trying to use the FindMethod to lookup if a Worksheet is present.
An effort to code as below. Seems I need further advise. Thankkful
for any help.

Brgds

CG Rosén


Dim nme As String
nme = TextBox1.Text & TextBox2.Text
Dim wk As Worksheets

Dim k
With ActiveWorkbook.Worksheets
Set k = .Find(????, LookIn:=????)

If Not k Is Nothing Then

code.....

End If
End With