Need help on how to search for worksheet name??

[mars91]

Hi all,

I will like to know how to search for worksheet name when using if then function??

What i am trying to do :

If Worksheet("abc") = Table.Cells(2, 2).Value Then

"Table" and "abc" are 2 different worksheets. I am looking on the cells(2,2) on table worksheet. The cells (2,2) have a name of abc. So if that cells is abc then it will search for a worksheet name with abc. Then i can continue with my if then condition.

I just wanting to know is there any search command for searching worksheet name??

Thanks..

 

[RSXchin]

activeworksheet.name

that should get you started

 

[mars91]

Hi rsxchin,

What do you mean by the code :

activeworksheet.name

I mean how to i merge into :

If Worksheet("abc") = Table.Cells(2, 2).Value Then

Srry,i do not really get it..

 

[Jerry Sullivan]

"Table" and "abc" are 2 different worksheets. I am looking on the cells(2,2) on table worksheet. The cells (2,2) have a name of abc. So if that cells is abc then it will search for a worksheet name with abc. Then i can continue with my if then condition.

I just wanting to know is there any search command for searching worksheet name??

Hi,

One approach would be to step through each worksheet in the workbook testing whether each worksheet's name is the value in Table.Cells(2,2).

However a more direct approach would be to just reference the sheet by name and see if it returns an error. If so, the sheet doesn't exist. If it doesn't trigger an error, you can go ahead and apply your if...then statements.

Something like....

Sub Find_Worksheet_Name()
    Dim strWSN As String
    strWSN = Table.Cells(2, 2).Value
    On Error Resume Next
    If IsError(Sheets(strWSN).Activate) Then
        MsgBox "Worksheet: " & strWSN & " not found."
        '...your code here...
    Else
       MsgBox "Do Then statements"
    End If
End Sub

 

[mars91]

Hi js411,

actually i do not need any msgbox. I was confused about the code you gave. I do not think i will get error. Well,allow me to explain it much more clearly..

If Worksheet("abc") = Table.Cells(Z, 2).Value Then

Actually, is not cells(2,2). Should be cells(z,2). I already have a do loop there.. Other than the worksheet "abc" ,there are still many other worksheets such as "cde" and "efg". I just wanted to compare on the z row , column 2 of the worksheet "Table".

I will alway see the name of the "Table" worksheet , not the other worksheets, then i will find for the worksheets with the same name as the cells(z,2) on the "Table" worksheet.

There should not have any error because,the total names on those z rows and column 2 is equal to the total numbers of worksheets with those names.

Hope it is not confusing..
Thanks.

 

[Jerry Sullivan]

The msgbox in my example was merely to allow you to follow the code in testing.
If you know that 100% of the time you will have a sheet name that matches the value in Table.Cells(Z, 2), then you are right, you will not have an error.

In that case, you really don't need if...then code. Just write....

With Sheets(Table.Cells(Z, 2))
     ...your code here for what you want to change on this sheet
End with

It's good practice in programming to allow for exception handling, so that is why I provided the example that tests what to do if that sheet name doesn't exist.

 

[Jerry Sullivan]

I just saw my note on where to put your code was in the wrong place on my first example. Correct location is below...

Sub Find_Worksheet_Name()
    Dim strWSN As String
    strWSN = Table.Cells(2, 2).Value
    On Error Resume Next
    If IsError(Sheets(strWSN).Activate) Then
        '...your code for what to do if sheet not found
    Else
        '...your code here for what to do on found sheet
    End If
End Sub

 

[mars91]

hi js411,

i did try your code but have error.. I mean i am quite confused of the code and i don't think that is what i want..

Well, actually a thing which i say was wrong.. The below code is wrong..

If Worksheet("abc") = Table.Cells(Z, 2).Value Then

This the right one..

[COLOR=blue]If Worksheet("") = Table.Cells(Z, 2).Value Then[/COLOR]

It should be something like this, but the Worksheet("") should have something in it.
I try to explain again..

Actually, the cells(z, 2) from the table worksheet will appear different names each time after it loop and increase by 1. So "If Worksheet("")" , i want to make it like example, for now this current cells(z, 2) have a name of "abc". Then it will search for the worksheet with the name "abc". Then after it loop and increase by 1,maybe the cells(z, 2) name is "efg".
Then it will search for the worksheet with the name "efg".

I thinking the ("") should have something in it. Should not be blank. Like some variable or some command. So it will select the worksheet with the same name on the current cells(z, 2). Thus, it will be easier as i don't need to program every single step.

Sorry if my explanation got problem or don't understand. Have any doubt please ask me again.
Thanks...

 

[Jerry Sullivan]

If you can post your code, I would probably be able to help you more easily.

I'm pretty sure I understand how your value in the table changes each loop and you want to do something with the sheet whose sheet name is shown in Table.Cells(Z, 2) during that loop. If I'm understanding you correctly, you don't need an If...then loop.

 

[mars91]

hi,
yes, you understand it correctly.

Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim sht3 As Worksheet
Dim sht4 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("Table")
Set sht2 = ThisWorkbook.Worksheets("apple")
Set sht3 = ThisWorkbook.Worksheets("pear")
Set sht4 = ThisWorkbook.Worksheets("banana")
z = 3
Do
If sht1.Cells(z, 1) = "apple" Then
If sht1.Cells(z, 4) = "abc" Then

This is a similar example of my code..Let me explain what i am trying to do.

I alway look at Table worksheet which is sht1, looking at the name inside the current cells(z, 1) first. There are about total 20 different names for the cells of (z, 1). The mean excluded the table worksheet, there will be 20 other worksheets which is same as the 20 names on those cells.

Then i will search and select the worksheet which have the same name as that current cells(z, 1).

After i select the worksheet, I will look at the cells(z, 4) of that table worksheet. The names on the cells(z, 4) will appear in that worksheet which was selected. Then i will proceed with the other steps which i want to do.. The are about total 100 plus names for cells(z, 4).
So if i do the code like above then i think i will do up to a thousand times??

Now you see, that why i trying to find or program for code which is able to complete this task..

Thank..