Search a value in all w/sheets and make a list.

[TUNGANA KURMA RAJU]

In all my w/sheets of my active w/book ,range("M5') has a value "pending" or
"completed". I need a function that returns list of those work sheet names
with"pending" value in range("M5").
Thank you all excel experts in advance.

 

[JLatham]

Can you use a User Defined Function (UDF)? If so, this should do the trick
for you. A UDF is a macro in the form of a Function that can be used in a
worksheet just like built-in Excel worksheet functions.

To insert this code into the workbook, press [Alt]+[F11] to open the VB
Editor, then choose Insert | Module to create a new code module. Copy the
code below and paste it into the module. Close the VB Editor.

Anywhere that you want to see the list, place this formula into the cell,
and format the cell to allow word-wrapping and make the row tall enough to
see multiple entries:
=wherearependings()

I hope this helps some. Here is the code to copy and paste:

Public Function WhereArePendings()
Dim anySheet As Worksheet
Dim testRange As Range

Application.Volatile
WhereArePendings = "None Pending"
For Each anySheet In Worksheets
Set testRange = anySheet.Range("M5")
If UCase(Trim(testRange)) = "PENDING" Then
If WhereArePendings = "None Pending" Then
WhereArePendings = anySheet.Name
Else
WhereArePendings = _
WhereArePendings & vbLf & anySheet.Name
End If
End If
Next
Set testRange = Nothing
End Function

 

[TUNGANA KURMA RAJU]

Thanks JLatham,
its working fine.Can you modify it,so that I can get the 'Pending'
sheetnames serially in a column,supose I put the formula in A1 ,out put comes
in Col A.
Anyway it is great,thanks once again.

Can you use a User Defined Function (UDF)? If so, this should do the trick
for you. A UDF is a macro in the form of a Function that can be used in a
worksheet just like built-in Excel worksheet functions.

To insert this code into the workbook, press [Alt]+[F11] to open the VB
Editor, then choose Insert | Module to create a new code module. Copy the
code below and paste it into the module. Close the VB Editor.

Anywhere that you want to see the list, place this formula into the cell,
and format the cell to allow word-wrapping and make the row tall enough to
see multiple entries:
=wherearependings()

I hope this helps some. Here is the code to copy and paste:

Public Function WhereArePendings()
Dim anySheet As Worksheet
Dim testRange As Range

Application.Volatile
WhereArePendings = "None Pending"
For Each anySheet In Worksheets
Set testRange = anySheet.Range("M5")
If UCase(Trim(testRange)) = "PENDING" Then
If WhereArePendings = "None Pending" Then
WhereArePendings = anySheet.Name
Else
WhereArePendings = _
WhereArePendings & vbLf & anySheet.Name
End If
End If
Next
Set testRange = Nothing
End Function

In all my w/sheets of my active w/book ,range("M5') has a value "pending" or
"completed". I need a function that returns list of those work sheet names
with"pending" value in range("M5").
Thank you all excel experts in advance.

 

[TUNGANA KURMA RAJU]

Thank you so much, can you modify the code for test range from sheet 2 to
end sheet of w/book.
Output heet names serially in rows,if i put this function in A1 of first
sheet,output should come A1:A range.

Can you use a User Defined Function (UDF)? If so, this should do the trick
for you. A UDF is a macro in the form of a Function that can be used in a
worksheet just like built-in Excel worksheet functions.

To insert this code into the workbook, press [Alt]+[F11] to open the VB
Editor, then choose Insert | Module to create a new code module. Copy the
code below and paste it into the module. Close the VB Editor.

Anywhere that you want to see the list, place this formula into the cell,
and format the cell to allow word-wrapping and make the row tall enough to
see multiple entries:
=wherearependings()

I hope this helps some. Here is the code to copy and paste:

Public Function WhereArePendings()
Dim anySheet As Worksheet
Dim testRange As Range

Application.Volatile
WhereArePendings = "None Pending"
For Each anySheet In Worksheets
Set testRange = anySheet.Range("M5")
If UCase(Trim(testRange)) = "PENDING" Then
If WhereArePendings = "None Pending" Then
WhereArePendings = anySheet.Name
Else
WhereArePendings = _
WhereArePendings & vbLf & anySheet.Name
End If
End If
Next
Set testRange = Nothing
End Function

In all my w/sheets of my active w/book ,range("M5') has a value "pending" or
"completed". I need a function that returns list of those work sheet names
with"pending" value in range("M5").
Thank you all excel experts in advance.

 

[JLatham]

Throw away/delete the code I gave you earlier. To do it this way, we need to
approach it in a different manner.

To put the code where it needs to be, open the workbook and select the
worksheet that you want the list to appear on and right-click it's name tab
and choose [View Code] from the list that appears. Copy the code below and
paste it into the module presented to you. Change either or both of the two
Const values as needed.

Now each time the sheet is selected it will examine cell M5 on all sheets
and list them in column A on this sheet beginning at A1. The list is
refreshed each time you choose this sheet.

Private Sub Worksheet_Activate()
Const baseCellAddress = "A1" ' change
Const pendingCell = "M5" ' may change
Dim anySheet As Worksheet
Dim testRange As Range
Dim baseCell As Range
Dim rowOffset As Long

Set baseCell = ActiveSheet.Range(baseCellAddress)
Columns("A:A").Clear ' clear out old results
For Each anySheet In Worksheets
Set testRange = anySheet.Range(pendingCell)
If UCase(Trim(testRange)) = "PENDING" Then
baseCell.Offset(rowOffset, 0) = _
anySheet.Name
rowOffset = rowOffset + 1
End If
Next
Set testRange = Nothing
End Sub

Thank you so much, can you modify the code for test range from sheet 2 to
end sheet of w/book.
Output heet names serially in rows,if i put this function in A1 of first
sheet,output should come A1:A range.

Can you use a User Defined Function (UDF)? If so, this should do the trick
for you. A UDF is a macro in the form of a Function that can be used in a
worksheet just like built-in Excel worksheet functions.

To insert this code into the workbook, press [Alt]+[F11] to open the VB
Editor, then choose Insert | Module to create a new code module. Copy the
code below and paste it into the module. Close the VB Editor.

Anywhere that you want to see the list, place this formula into the cell,
and format the cell to allow word-wrapping and make the row tall enough to
see multiple entries:
=wherearependings()

I hope this helps some. Here is the code to copy and paste:

Public Function WhereArePendings()
Dim anySheet As Worksheet
Dim testRange As Range

Application.Volatile
WhereArePendings = "None Pending"
For Each anySheet In Worksheets
Set testRange = anySheet.Range("M5")
If UCase(Trim(testRange)) = "PENDING" Then
If WhereArePendings = "None Pending" Then
WhereArePendings = anySheet.Name
Else
WhereArePendings = _
WhereArePendings & vbLf & anySheet.Name
End If
End If
Next
Set testRange = Nothing
End Function

In all my w/sheets of my active w/book ,range("M5') has a value "pending" or
"completed". I need a function that returns list of those work sheet names
with"pending" value in range("M5").
Thank you all excel experts in advance.

 

[TUNGANA KURMA RAJU]

Thank you JLatham,
Really u are great and genius.
How learn this wonderful Vba?
Can u please suggest me any books or tutorials where I can learn them easily.
Thank u so much !!

Throw away/delete the code I gave you earlier. To do it this way, we need to
approach it in a different manner.

To put the code where it needs to be, open the workbook and select the
worksheet that you want the list to appear on and right-click it's name tab
and choose [View Code] from the list that appears. Copy the code below and
paste it into the module presented to you. Change either or both of the two
Const values as needed.

Now each time the sheet is selected it will examine cell M5 on all sheets
and list them in column A on this sheet beginning at A1. The list is
refreshed each time you choose this sheet.

Private Sub Worksheet_Activate()
Const baseCellAddress = "A1" ' change
Const pendingCell = "M5" ' may change
Dim anySheet As Worksheet
Dim testRange As Range
Dim baseCell As Range
Dim rowOffset As Long

Set baseCell = ActiveSheet.Range(baseCellAddress)
Columns("A:A").Clear ' clear out old results
For Each anySheet In Worksheets
Set testRange = anySheet.Range(pendingCell)
If UCase(Trim(testRange)) = "PENDING" Then
baseCell.Offset(rowOffset, 0) = _
anySheet.Name
rowOffset = rowOffset + 1
End If
Next
Set testRange = Nothing
End Sub

Thank you so much, can you modify the code for test range from sheet 2 to
end sheet of w/book.
Output heet names serially in rows,if i put this function in A1 of first
sheet,output should come A1:A range.

Can you use a User Defined Function (UDF)? If so, this should do the trick
for you. A UDF is a macro in the form of a Function that can be used in a
worksheet just like built-in Excel worksheet functions.

To insert this code into the workbook, press [Alt]+[F11] to open the VB
Editor, then choose Insert | Module to create a new code module. Copy the
code below and paste it into the module. Close the VB Editor.

Anywhere that you want to see the list, place this formula into the cell,
and format the cell to allow word-wrapping and make the row tall enough to
see multiple entries:
=wherearependings()

I hope this helps some. Here is the code to copy and paste:

Public Function WhereArePendings()
Dim anySheet As Worksheet
Dim testRange As Range

Application.Volatile
WhereArePendings = "None Pending"
For Each anySheet In Worksheets
Set testRange = anySheet.Range("M5")
If UCase(Trim(testRange)) = "PENDING" Then
If WhereArePendings = "None Pending" Then
WhereArePendings = anySheet.Name
Else
WhereArePendings = _
WhereArePendings & vbLf & anySheet.Name
End If
End If
Next
Set testRange = Nothing
End Function


:

In all my w/sheets of my active w/book ,range("M5') has a value "pending" or
"completed". I need a function that returns list of those work sheet names
with"pending" value in range("M5").
Thank you all excel experts in advance.

 

[JLatham]

You're very welcome. Glad I was able to assist.

I've learned simply from doing. I started learning BASIC back as early as
1979, and on to Visual Basic when it was introduced and extended that to VBA.
There are a number of site around the net to help get started.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.the-excel-advisor.com/excel-macros-vba-tutorial.html
http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm
http://www.exceltip.com/excel_links.html

there are other sites that provide usefull information about specific issues.
http://www.contextures.com/
http://www.cpearson.com/
http://www.j-walk.com/
http://www.mcgimpsey.com/
http://www.rondebruin.nl/
http://www.mrexcel.com

Thank you JLatham,
Really u are great and genius.
How learn this wonderful Vba?
Can u please suggest me any books or tutorials where I can learn them easily.
Thank u so much !!

Throw away/delete the code I gave you earlier. To do it this way, we need to
approach it in a different manner.

To put the code where it needs to be, open the workbook and select the
worksheet that you want the list to appear on and right-click it's name tab
and choose [View Code] from the list that appears. Copy the code below and
paste it into the module presented to you. Change either or both of the two
Const values as needed.

Now each time the sheet is selected it will examine cell M5 on all sheets
and list them in column A on this sheet beginning at A1. The list is
refreshed each time you choose this sheet.

Private Sub Worksheet_Activate()
Const baseCellAddress = "A1" ' change
Const pendingCell = "M5" ' may change
Dim anySheet As Worksheet
Dim testRange As Range
Dim baseCell As Range
Dim rowOffset As Long

Set baseCell = ActiveSheet.Range(baseCellAddress)
Columns("A:A").Clear ' clear out old results
For Each anySheet In Worksheets
Set testRange = anySheet.Range(pendingCell)
If UCase(Trim(testRange)) = "PENDING" Then
baseCell.Offset(rowOffset, 0) = _
anySheet.Name
rowOffset = rowOffset + 1
End If
Next
Set testRange = Nothing
End Sub

Thank you so much, can you modify the code for test range from sheet 2 to
end sheet of w/book.
Output heet names serially in rows,if i put this function in A1 of first
sheet,output should come A1:A range.

:

Can you use a User Defined Function (UDF)? If so, this should do the trick
for you. A UDF is a macro in the form of a Function that can be used in a
worksheet just like built-in Excel worksheet functions.

To insert this code into the workbook, press [Alt]+[F11] to open the VB
Editor, then choose Insert | Module to create a new code module. Copy the
code below and paste it into the module. Close the VB Editor.

Anywhere that you want to see the list, place this formula into the cell,
and format the cell to allow word-wrapping and make the row tall enough to
see multiple entries:
=wherearependings()

I hope this helps some. Here is the code to copy and paste:

Public Function WhereArePendings()
Dim anySheet As Worksheet
Dim testRange As Range

Application.Volatile
WhereArePendings = "None Pending"
For Each anySheet In Worksheets
Set testRange = anySheet.Range("M5")
If UCase(Trim(testRange)) = "PENDING" Then
If WhereArePendings = "None Pending" Then
WhereArePendings = anySheet.Name
Else
WhereArePendings = _
WhereArePendings & vbLf & anySheet.Name
End If
End If
Next
Set testRange = Nothing
End Function


:

In all my w/sheets of my active w/book ,range("M5') has a value "pending" or
"completed". I need a function that returns list of those work sheet names
with"pending" value in range("M5").
Thank you all excel experts in advance.