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..

 

[mikerickson]

Why loop, testing

If Worksheets("abc").Name = table.Range("B2").Value Then
    Rem do something
End If

Why not just grab the sheet directly

    On Error Goto NotThere
    With Worksheets(table.Range("B2").Value)
        Rem do something
    End With
NotThere:
    On Error Goto 0

 

[Jerry Sullivan]

Hmmm...that looks familiar to me.

 

[mars91]

hi mikerickson,
can you explain,i never use with end with code before.

hi js411,
hmm, so you have any idea how i can do it? You have a slight idea of what i am trying to do right? Can understand?

I appreciate any help.
thank you..

 

[Jerry Sullivan]

Can you post the rest of your code?

If you don't have any more code, please explain what change you are trying to make to each sheet. That will allow us to show you how to incorporate this into your overall Sub procedure.

 

[mars91]

This the similar example of the orignal code..

Dim c(), i&, x
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("Table")
Set sht2 = ThisWorkbook.Worksheets("apple")
z = 3
Do
 
If sht1.Cells(z, 1) = "apple" Then
If sht1.Cells(z, 4) = "abc" Then
 
Set d = CreateObject("scripting.dictionary")
With Sheets("apple")
nr = .Range("E:F").Find("*", searchorder:=xlByRows, _
    searchdirection:=xlPrevious).Row
a = .Range("E:F").Resize(nr)
End With
ReDim c(1 To nr, 1 To 6)
For i = 1 To nr
    x = a(i, 1)
 
    If x = "abc" Then
 
    If Not d.exists(x) Then
        d.Add x, d.Count + 1
        c(d(x), 1) = a(i, 1)
        If Not IsEmpty(a(i, 2)) Then
            c(d(x), 4) = 1
            c(d(x), 5) = a(i, 2)
            c(d(x), 6) = a(i, 2) ^ 2
        End If
    Else
        If Not IsEmpty(a(i, 2)) Then
            c(d(x), 4) = c(d(x), 4) + 1
            c(d(x), 5) = c(d(x), 5) + a(i, 2)
            c(d(x), 6) = c(d(x), 6) + a(i, 2) ^ 2
        End If
    End If
End If
Next i
For i = 1 To d.Count
    c(i, 2) = c(i, 5) / c(i, 4)
    c(i, 3) = ((c(i, 6) - c(i, 2) * c(i, 5)) / (c(i, 4) - 1)) ^ 0.5
Next i
With Sheets("Table")
.Range("D3").Resize(d.Count, 3) = c
.Range("E2").Resize(, 3) = Array("Item", "Ave", "1 sigma")
End With
 
End If
End If
 
z = z + 1
Loop While sht1.Cells(z, 1) <> ""
End Sub

ok,this is what i am doing, like what i have mention in post #10...

Look at the name in that current cells(z,1) then find for the worksheet which have the same name as that current cell. Then select that worksheet.

After that,look at the cells(z, 4) of the table worksheet for another name on that current cell. Then go to that selected worksheet find for all the name which is same as the cells(z, 4) in table worksheet. Then calculate the average and standard deviation of those same name.

The middle part of the code is to calculate the average and standard deviation.. This middle code was inside my another threat. The code for calculation was given by a helpful guy.

For now, i have problem with the search and select worksheets thingy like i mention on previous post. Because i have around 20 different names for those cells(z, 1) and up to 100 plus different names in those cells(z, 4) of the table worksheet.. (Note that the total numbers of names on cells(z, 1) = to the total numbers of worksheets. Both will have the same names.)

Thus, it is not possible to do keep program the if then if then like the above beacause i will type up to thousands if then statments to complete my task.

Thank you...

 

[Jerry Sullivan]

What is the sequence of combinations of Column A and Column D items you want to process?

Is it
Column A3 + D3 then
Column A3 + D4 then
Column A3 + D5 ....etc

or
Column A3 + D3 then
Column A4 + D3 then
Column A5 + D3....etc

 

[mars91]

Hi,

start from column A row 3 and column D row 3 then it will loop and increase row by 1. Both of those cells contains names.

So looking on column A row 3 for it's name. Example if that cell have a name of "apple". Then will search for a worksheet name "apple".

So now go to column D row 3, look for it's name. Example if that cell have a name call "abc". Then i will go to the "apple" worksheet and find for the name "abc" in that worksheet.

Can you get it??

I am abit counfused about the sequence of combinations that you mention.

I think should be Column A3 , look for worksheet name that is same as Column A3. Then Go to Column D3 look at the name. Then again go to the worksheet name that is same as Column A3. Then look for the name which is same as Column D3.

Thanks..

 

[Jerry Sullivan]

Yes, so the first combination is:
Go to Worksheet: apple then find the text "abc"

Is your next combination:
Go to Worksheet: apple then find the text "def" (if "def" is in Cell D4)?

 

[mars91]

Hi,
yes, is nearly there. The first combination is right.

Well it will depend on what is the name on cells(z, 1) of the "Table" worksheet. (Note that z = 3, ihave the loop and increase function) Because on the column A row 3 have the name apple. So : Go to worksheet with the name "apple" then find the text "abc".

The colmun D row 3 name is "abc". But on the column D row 4 will also have a randomly names out of that list of 100+ names.

Can you get it??
If not i can try to post a picture.

Thanks..

 

[Jerry Sullivan]

The code below will show you one way to find your worksheet-text name matches and report them back the the summary sheet.

I've stayed away from your std dev calculations. If you need help incorporating those, I'd suggest you start another thread.

Sub Mars91()
    Dim lrowA As Long, lrowD As Long, lRowE As Long
    Dim strMatch As String
    Dim sht1 As Worksheet
    Set sht1 = ThisWorkbook.Worksheets("Table")
 
    Dim rngNamesA As Range 'about 20 worksheet names
    Dim rngNamesD As Range 'about 100 text strings to match
    Dim rngMatch As Range
    On Error Resume Next
    Application.ScreenUpdating = False
    With sht1
        Set rngNamesA = .Range("A3:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        Set rngNamesD = .Range("D3:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
        lRowE = 3
        For lrowA = 1 To rngNamesA.Rows.Count
            For lrowD = 1 To rngNamesD.Rows.Count
                strMatch = rngNamesD(lrowD).Value
                With Sheets(rngNamesA(lrowA).Value)
                    Set rngMatch = .Range("E:F").Find(strMatch, searchorder:=xlByRows, _
                        searchdirection:=xlPrevious)
                    If rngMatch Is Nothing Then
                        MsgBox strMatch & " not found on Sheet: " & _
                            rngNamesA(lrowA).Value
                        Exit Sub
                    End If
                End With
                With Sheets("Table")
                    .Cells(lRowE, 5) = "On Worksheet: " & rngNamesA(lrowA) & _
                        " | Text: " & strMatch & " was found on Row: " & rngMatch.Row
                End With
                lRowE = lRowE + 1
            Next lrowD
        Next lrowA
    End With
    Set rngNamesA = Nothing
    Set rngNamesD = Nothing
End Sub

The end result will look something like this. You can use the information that is now being displayed in Col E in your std dev. calcs.

Excel Workbook
	A	B	C	D	E
1
2	Sheet name			Text to find	Result
3	Apple1			abc	On Worksheet: Apple1 | Text: abc was found on Row: 9
4	Apple2			def	On Worksheet: Apple1 | Text: def was found on Row: 10
5	Apple3			hij	On Worksheet: Apple1 | Text: hij was found on Row: 11
6				klm	On Worksheet: Apple1 | Text: klm was found on Row: 12
7					On Worksheet: Apple2 | Text: abc was found on Row: 6
8					On Worksheet: Apple2 | Text: def was found on Row: 7
9					On Worksheet: Apple2 | Text: hij was found on Row: 8
10					On Worksheet: Apple2 | Text: klm was found on Row: 9
11					On Worksheet: Apple3 | Text: abc was found on Row: 10
Table