Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
thephoenix12
 
Posts: n/a
Default alternative to msgbox


Hi,

I have a macro that searches for something in multiple worksheets, and
each time it finds what it is looking for, a message box pops
displaying the name of the worksheet. Instead of this, I would like it
to display all the worksheet names on a notepad sheet, or word file, or
something that is printable.

Thanks for your help,

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #2   Report Post  
anilsolipuram
 
Posts: n/a
Default


This would save the worksheet names to c:\worksheet_names.txt

Sub MACRO9()
Dim W As Worksheet
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VAL = InputBox("Enter which cell to search")
For Each W In Worksheets
W.Select
If W.Name < sh_skip Then
If (IsNumeric(Range(VAL).Value) And Range(VAL).Value < "")
Then
temp = temp & W.Name & Chr(10)
End If
End If
Next
Workbooks.Add
temp1 = Split(temp, Chr(10))
Range("a1").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\worksheet_names.txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #3   Report Post  
Andibevan
 
Posts: n/a
Default

Hi ,.

If you post your existing code (would suggest you use the excel.programming
forum) it would help.

Ta

Andi

"thephoenix12"
wrote in message
news:thephoenix12.1r16uq_1119449139.1649@excelforu m-nospam.com...

Hi,

I have a macro that searches for something in multiple worksheets, and
each time it finds what it is looking for, a message box pops
displaying the name of the worksheet. Instead of this, I would like it
to display all the worksheet names on a notepad sheet, or word file, or
something that is printable.

Thanks for your help,

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile:
http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213


  #4   Report Post  
thephoenix12
 
Posts: n/a
Default


Thanks anilsolipuram,

Is there a way to have the file open when the macro is done? And also,
if it is not too much trouble, can you go through the code and explain
some parts of it, like the Chr(10) part?

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #5   Report Post  
Earl Kiosterud
 
Posts: n/a
Default

Steve,

This will populate a sheet called "Hits" with your list, to give you the
idea of a possible solution. Create the sheet first. Variable SheetName is
the name your code has found.

Dim Index as long
Index = 1

At the place in your code where the message box is,

Sheets("Hits").Cells(Index, 1) = SheetName
Index = Index + 1

Untested.

--
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"thephoenix12"
wrote in message
news:thephoenix12.1r16uq_1119449139.1649@excelforu m-nospam.com...

Hi,

I have a macro that searches for something in multiple worksheets, and
each time it finds what it is looking for, a message box pops
displaying the name of the worksheet. Instead of this, I would like it
to display all the worksheet names on a notepad sheet, or word file, or
something that is printable.

Thanks for your help,

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile:
http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213





  #6   Report Post  
thephoenix12
 
Posts: n/a
Default


The code that anilsolipuram wrote in the first reply is my existing code
plus what he added to put the results in a text file. (He was very
helpful and wrote my existing code yesterday). Right now I am just
wondering if there is a simple way to have the text file open when the
macro is done; and I would also like to know how the newly written code
(the part that writes the results to a text file) works.

Eventually I am going to want the code to search through a range of
cells, not just one cell. For example D9:D30, and have the results
displayed in the text file (with a space or something to separate each
cells results) So instead of entering a single cell for VAL, I am
going to want to enter a range of cells, then have the macro perform
its search for each of those cells. I am trying to figure this out
now, but am not having much luck. If anyone has any suggestions I
would be very grateful.

Thanks,

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #7   Report Post  
anilsolipuram
 
Posts: n/a
Default


This will open the text file in the excel file itself, if you want we
can automate the printing part also

Sub MACRO9()
Dim W As Worksheet
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VAL = InputBox("Enter which cell to search")
For Each W In Worksheets
W.Select
If W.Name < sh_skip Then
If (IsNumeric(Range(VAL).Value) And Range(VAL).Value < "")
Then
temp = temp & W.Name & Chr(10)
End If
End If
Next
Workbooks.Add
temp1 = Split(temp, Chr(10))
Range("a1").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\worksheet_name.txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\worksheet_name.txt"

End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #8   Report Post  
anilsolipuram
 
Posts: n/a
Default


Explanation to my previous post

temp = temp & W.Name & Chr(10) (1)
'code
'code

Workbooks.Add (2)
temp1 = Split(temp, Chr(10)) (3)
Range("a1").Select (4)
For i = 0 To UBound(temp1) (5)
Selection.Value = temp1(i) (6)
ActiveCell.Offset(1, 0).Select (7)
Next
Application.DisplayAlerts = False (8)

ActiveWorkbook.SaveAs Filename:= _
"C:\worksheet_name.txt", _
FileFormat:=xlText, CreateBackup:=False (9)
ActiveWorkbook.Save
ActiveWorkbook.Close(10)
Application.DisplayAlerts =false (11)
Workbooks.OpenText Filename:="C:\worksheet_name.txt" (12)

(1) temp variable stores all the worksheet names with chr(10) , new
line charecter between sheet names
(2) add new workbook
(3)split the temp variable to get individual sheet names
(4) select a1 cell in new workbook
(5)(6)(7) loop through all the worksheet variables and put the variable
in column a of new workbook
(8) disable alerts
(9) save the new workbook created as text file in c:\
(10)(11) save and close the new workbook
(12) open the text file in excel.
(1)


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #9   Report Post  
thephoenix12
 
Posts: n/a
Default


Thanks anilsolipuram, that works perfectly. Now im trying to change it
so it works through a range of cells; basically the same as a user
running it for D9, then D10, then D11, etc. etc., except it would do it
all at once.
It seems to me that I can leave this line:
*"VAL = InputBox("enter which cell to search")*
the same...just have a user input a range instead. I'm then trying to
add a For statement after
*"If W.Name < sh_skip Then" *
and then change the next line
*"IF (IsNumeric(Range(VAL).Value) And Range(VAL).Value < "") Then" *
so it searches a single cell in the range. I am not having much luck
though, and any help would be appreciated.

Thanks,

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #10   Report Post  
anilsolipuram
 
Posts: n/a
Default


if you want for cells say d9,d10,d11 , you want all the cells to be
numeric or any one of them should be numeric , what is the criteria for
multiple cells.


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213



  #11   Report Post  
thephoenix12
 
Posts: n/a
Default


Basically I want the macro to do the same thing it does now, just do it
for more than one cell. So I want it to find the worksheets on which
D9 has numbers, then I want it to find the worksheets on which D10 has
numbers, etc, etc.


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #12   Report Post  
anilsolipuram
 
Posts: n/a
Default


you have input the range like , d5:d11,c3,a2




Sub MACRO9()
Dim W As Worksheet
Dim range_input, e_range As Range
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VAL = InputBox("Enter which cell to search")
Set range_input = Range(VAL)
For Each e_range In range_input
temp = temp & e_range.Address & Chr(10)
For Each W In Worksheets
W.Select
If W.Name < sh_skip Then
If (IsNumeric(Range(e_range.Address).Value) And
Range(e_range.Address).Value < "") Then
temp = temp & W.Name & Chr(10)

End If
End If
Next
Next
Workbooks.Add
temp1 = Split(temp, Chr(10))
Range("a1").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\worksheet_name.txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\worksheet_name.txt"
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #13   Report Post  
thephoenix12
 
Posts: n/a
Default


Yes that is what I wanted. Does e_range represent a single cell in a
range?

There is one final thing I want to do with this. When the macro is
done, lets say we were searching through a range of D9:D11, it prints
out a sheet looking like this:

$D$9
Worksheet 2
Worksheet 5
$D$10
Worksheet 9
Worksheet 16
$D$11
Worksheet 3

Two columns to the left of column D, there are names corresponding to
D9, D10, etc. For example, the name in B9 (same throughout the cells
in all worksheets except for the "summary" worksheet) corresponds to
the results of cells D9, and the name in B10 corresponds to the results
from cells D10. Would it be possible to replace the $D$9 with the name
in cell B9, $D$10 with the name in B10, etc?


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #14   Report Post  
thephoenix12
 
Posts: n/a
Default


I see that we need to replace the "e_range.Address" in this:

TEMP = TEMP & E_RANGE.ADDRESS & CHR(10)

I am not sure, however, what to replace it with, to get the names from
column B.


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #15   Report Post  
anilsolipuram
 
Posts: n/a
Default


You are correct, e_range represent single cell in a range of cells(cells
address you enter)

Yes temp = temp & e_range.Address & Chr(10) is the line you have to
change


it should be

temp = temp & range(e_range.Address).value & Chr(10)


Sub MACRO9()
Dim W As Worksheet
Dim range_input, e_range As Range
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VAL = InputBox("Enter which cell to search")
Set range_input = Range(VAL)
For Each e_range In range_input
temp = temp & range(e_range.Address).value & Chr(10)
For Each W In Worksheets
W.Select
If W.Name < sh_skip Then
If (IsNumeric(Range(e_range.Address).Value) And
Range(e_range.Address).Value < "") Then
temp = temp & W.Name & Chr(10)

End If
End If
Next
Next
Workbooks.Add
temp1 = Split(temp, Chr(10))
Range("a1").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\worksheet_name.txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\worksheet_name.txt"
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213



  #16   Report Post  
anilsolipuram
 
Posts: n/a
Default


minor error change it should be

temp = temp & range("b" & e_range.row).value & Chr(10)


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #17   Report Post  
thephoenix12
 
Posts: n/a
Default


Yup that works great! Thanks for all your help anilsolipuram!!!


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #18   Report Post  
thephoenix12
 
Posts: n/a
Default


Haha, I was just checking over it and I have one more minor request. Is
there a simple way to make the names displayed in bold text? Something
like

temp = temp & -(insert something to make this bold)- Range("b" &
e_range.Row).Value -(insert something to end the bold statement)- &
Chr(10)


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #19   Report Post  
anilsolipuram
 
Posts: n/a
Default


Doesnot work like html

Range("a5").Select
Selection.Font.Bold = True

will change cell a5 to bold


Range("a5").Select
ActiveCell.Characters(Start:=1, Length:=7).Font.FontStyle = "Bold"

will change first 7 letters of the cell a5 to bold


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #20   Report Post  
thephoenix12
 
Posts: n/a
Default


Is there a way I can use that to make just the names bold? Or maybe if
I added a symbol or something before each name in the code, and then
had the macro search for that symbol, and when it finds it change the
cell it is in to bold. That is probably really complicated though and
I just need something simple.


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213



  #21   Report Post  
anilsolipuram
 
Posts: n/a
Default


Do you want bold the text in textfile or that in the original excel
file, you cannot bold text in textfile by using macro


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #22   Report Post  
thephoenix12
 
Posts: n/a
Default


I would like to bold the text of the names only (row B) on the new excel
file that is created when the macro is run. Even though the original
text is bold, it is not on the new excel file. Any ideas?


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #23   Report Post  
anilsolipuram
 
Posts: n/a
Default


This will create excel file instead of textfile we previous had, makes
the text bold




Sub MACRO9()
Dim W As Worksheet
Dim range_input, e_range As Range
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VAL = InputBox("Enter which cell to search")
Set range_input = Range(VAL)
For Each e_range In range_input
temp = temp & Range("b" & e_range.Row).Value & Chr(10)
For Each W In Worksheets
W.Select
If W.Name < sh_skip Then
If (IsNumeric(Range(e_range.Address).Value) And
Range(e_range.Address).Value < "") Then
temp = temp & W.Name & Chr(10)

End If
End If
Next
Next
Workbooks.Add
temp1 = Split(temp, Chr(10))
Range("a1").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
ActiveCell.Font.Bold = True
ActiveCell.Offset(1, 0).Select

Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:="C:\sheetname.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False

ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\sheetname.xls"
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #24   Report Post  
thephoenix12
 
Posts: n/a
Default


That makes everything in the new worksheet bold. Is there a way to make
only the names (obtained from column B, in this part of the code "temp =
temp & Chr(10) & Range("b" & e_range.Row).Value & Chr(10)") bold, on the
new worksheet?


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #25   Report Post  
thephoenix12
 
Posts: n/a
Default


Im thinking of doing something like this: the names are all under 15
characters, everything else is larger, and they all contain no numbers,
as compared to mostly everything else.

So what if we were to insert and If statement saying If cell has no
numbers and has less than 15 characters, Then Selection.Font.Bold =
True End If. As you can see though, I dont know how to do the first
part with the numbers and the characters.


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213



  #26   Report Post  
anilsolipuram
 
Posts: n/a
Default


try this now

Sub MACRO99()
Dim W As Worksheet
Dim range_input, e_range As Range
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VAL = InputBox("Enter which cell to search")
Set range_input = Range(VAL)
For Each e_range In range_input
temp = temp & "bold" & Range("b" & e_range.Row).Value & Chr(10)
For Each W In Worksheets
W.Select
If W.Name < sh_skip Then
If (IsNumeric(Range(e_range.Address).Value) And
Range(e_range.Address).Value < "") Then
temp = temp & W.Name & Chr(10)

End If
End If
Next
Next
MsgBox temp
Workbooks.Add
temp1 = Split(temp, Chr(10))
Range("a1").Select

Dim ch_bold As Variant

For i = 0 To UBound(temp1)
ch_bold = Split(temp1(i), "bold")
If (UBound(ch_bold) 0) Then

Selection.Value = ch_bold(1)

ActiveCell.Font.Bold = True
Else
Selection.Value = temp1(i)
End If
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:="C:\sheetname.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\sheetname.xls"
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #27   Report Post  
thephoenix12
 
Posts: n/a
Default


Hey, right now I am trying to clean this macro up so that, on the final
excel output there are no two blank spaces in a row...let me explain;
this is a sample output excel file

Person 1
Project 3
Project 6

Person 2
Project 1



Person 3
Project 7

The extra spaces come in because there is a space on the original excel
file, because of the way it is organized. So when the program outputs
the value of the corresponding cell in column b, it is only outputting
a blank cell. I am trying to write the program so that it skips cells
in column b when there are blank spaces, however I keep getting an
error message. Here is the program with what I have added in bold:

Sub ProjectSearch()
Dim W As Worksheet
Dim range_input, e_range As Range
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VAL = InputBox("Enter which range to search in:")
Set range_input = Range(VAL)

For Each e_range In range_input
IF RANGE(\"B\" & E_RANGE.ROW).VALUE.CHARACTERS.COUNT 0 THEN
temp = temp & Chr(10) & Range("b" & e_range.Row).Value & Chr(10)
END IF
For Each W In Worksheets
W.Select
If W.Name < sh_skip Then
If (IsNumeric(Range(e_range.Address).Value) And
Range(e_range.Address).Value < "") Then
temp = temp & W.Name & Chr(10)

End If
End If
Next
Next
Workbooks.Add
Range("a1").Select
Selection.Value = "Title"
Selection.Font.Bold = True
temp1 = Split(temp, Chr(10))
Range("a2").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
If ActiveCell.Characters.Count < 13 Then
ActiveCell.Font.Bold = True
End If
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\srh.HSNPARCH\Desktop\test.txt", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\Documents and
Settings\srh.HSNPARCH\Desktop\test.txt"
End Sub

Do you have any ideas on what I should do?

Thanks,

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #28   Report Post  
anilsolipuram
 
Posts: n/a
Default


try with

if trim(Range("b" & e_range.Row).Value)<"" Then


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #29   Report Post  
anilsolipuram
 
Posts: n/a
Default


try with

if trim(Range("b" & e_range.Row).Value)<"" Then


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #30   Report Post  
thephoenix12
 
Posts: n/a
Default


Thanks that works


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213



  #31   Report Post  
thephoenix12
 
Posts: n/a
Default


Here is the macro with what you recommended to do anilsolipuram, now
that I am trying to change up the macro.
When I run this, what it does is it lists a person from column b, and
then it just lists all the worksheet names. Let me try to explain what
I am trying to do now a little better. The spreadsheet is set up so
that peoples names are in column b, and each worksheet represents a new
project. When they are working on it, they have a number in column d,
or e, or whatever (depending on the week). What I would like to do now
is to do the reverse of what we had done earlier, which was create a
list with the peoples names and the projects (worksheets) they were
working on. I would like to create a list that has each project
(worksheet), and under each project, lists the people working on it
(corresponding cell will not be blank). This is why the W.Name part
should be first I think, before the part where it lists the people.

Sub PeopleSearch()
Dim W As Worksheet
Dim range_input, e_range As Range
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VAL = InputBox("Enter which range to search in:")
Set range_input = Range(VAL)

FOR EACH E_RANGE IN RANGE_INPUT
IF TRIM(RANGE(\"B\" & E_RANGE.ROW).VALUE) < \"\" THEN
TEMP = TEMP & CHR(10) & RANGE(\"B\" & E_RANGE.ROW).VALUE & CHR(10)
END IF
FOR EACH W IN WORKSHEETS
W.SELECT
IF W.NAME < SH_SKIP THEN
IF TRIM(RANGE(\"B\" & E_RANGE.ROW).VALUE) < \"\" THEN
TEMP = TEMP & W.NAME & CHR(10)

END IF
END IF
NEXT
NEXT

Workbooks.Add
Range("a1").Select
Selection.Value = "PROJECTS PEOPLE ARE WORKING ON"
Selection.Font.Bold = True
temp1 = Split(temp, Chr(10))
Range("a2").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\srh.HSNPARCH\Desktop\testing.txt", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\Documents and
Settings\srh.HSNPARCH\Desktop\testing.txt"
End Sub


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #32   Report Post  
anilsolipuram
 
Posts: n/a
Default


I think I got your point.

Try this and let me know

Sub PeopleSearch()
Dim W As Worksheet
Dim range_input, e_range As Range
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VAL = InputBox("Enter which range to search in:")
Set range_input = Range(VAL)
For Each W In Worksheets
W.Select
temp = temp & W.Name & Chr(10)

For Each e_range In range_input
If W.Name < sh_skip Then

If Trim(Range("b" & e_range.Row).Value) < "" Then
temp = temp & W.Range("b" & e_range.Row).Value & Chr(10)

End If
End If
Next
Next

Workbooks.Add
Range("a1").Select
Selection.Value = "PROJECTS PEOPLE ARE WORKING ON"
Selection.Font.Bold = True
temp1 = Split(temp, Chr(10))
Range("a2").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\srh.HSNPARCH\Desktop\testing.txt", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\Documents and
Settings\srh.HSNPARCH\Desktop\testing.txt"
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #33   Report Post  
thephoenix12
 
Posts: n/a
Default


That sort of works. This part needs to be changed:

IF TRIM(RANGE(\"B\" & E_RANGE.ROW).VALUE) < \"\" THEN

I need it to be not the part from row b, but actually cells within the
range that were entered. But if I try to just put

IF TRIM(RANGE(E_RANGE).VALUE) < \"\" THEN

it gives me an error. Just to check to see if this worked though, I
had my range be in column d, and replaced the "b" with "d" and it did
work. So I just need to know what to put instead of just e_range.

Thanks,

Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #34   Report Post  
anilsolipuram
 
Posts: n/a
Default


minor change in code



Sub PeopleSearch()
Dim W As Worksheet
Dim range_input, e_range As Range
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VAL = InputBox("Enter which range to search in:")
Set range_input = Range(VAL)
For Each W In Worksheets
W.Select
temp = temp & W.Name & Chr(10)

For Each e_range In range_input
If W.Name < sh_skip Then

If Trim(e_range.Value) < "" Then
temp = temp & W.Range("b" & e_range.Row).Value & Chr(10)

End If
End If
Next
Next

Workbooks.Add
Range("a1").Select
Selection.Value = "PROJECTS PEOPLE ARE WORKING ON"
Selection.Font.Bold = True
temp1 = Split(temp, Chr(10))
Range("a2").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\srh.HSNPARCH\Desktop\testing.txt", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\Documents and
Settings\srh.HSNPARCH\Desktop\testing.txt"
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #35   Report Post  
thephoenix12
 
Posts: n/a
Default


I don't know why it is doing this, but whatever worksheet I had last
active before I run the macro, it only returns the people working on
that project (worksheet), but says they are working on every single
one.


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213



  #36   Report Post  
anilsolipuram
 
Posts: n/a
Default


Ok try it now


Sub PeopleSearch()
Dim W As Worksheet
Dim range_input, e_range As Range
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VAL = InputBox("Enter which range to search in:")
Set range_input = Range(VAL)
For Each W In Worksheets
W.Select
temp = temp & W.Name & Chr(10)

For Each e_range In range_input
If W.Name < sh_skip Then

If Trim(W.Range(e_range.Address).Value) < "" Then
temp = temp & W.Range("b" & e_range.Row).Value & Chr(10)

End If
End If
Next
Next

Workbooks.Add
Range("a1").Select
Selection.Value = "PROJECTS PEOPLE ARE WORKING ON"
Selection.Font.Bold = True
temp1 = Split(temp, Chr(10))
Range("a2").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\srh.HSNPARCH\Desktop\testing.txt", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\Documents and
Settings\srh.HSNPARCH\Desktop\testing.txt"
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #37   Report Post  
thephoenix12
 
Posts: n/a
Default


That works! Thanks so much anilsolipuram!!


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Msgbox saying "Unable to read file" lashio Excel Discussion (Misc queries) 3 May 30th 05 07:59 PM
Seeking an alternative to blank ("") cell in IF arguments KG Excel Discussion (Misc queries) 4 May 30th 05 01:11 AM
Msgbox based on If statement Neil Jimack Excel Worksheet Functions 2 May 11th 05 11:22 PM
Msgbox Wildman Excel Worksheet Functions 1 April 26th 05 04:57 AM
Help with MsgBox... tjb Excel Worksheet Functions 3 December 29th 04 04:43 PM


All times are GMT +1. The time now is 12:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"