A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

alternative to msgbox



 
 
Thread Tools Display Modes
  #31  
Old June 27th, 2005, 07:47 PM
thephoenix12
external usenet poster
 
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  
Old June 27th, 2005, 07:59 PM
anilsolipuram
external usenet poster
 
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  
Old June 27th, 2005, 08:15 PM
thephoenix12
external usenet poster
 
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  
Old June 27th, 2005, 08:21 PM
anilsolipuram
external usenet poster
 
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  
Old June 27th, 2005, 08:38 PM
thephoenix12
external usenet poster
 
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  
Old June 27th, 2005, 08:46 PM
anilsolipuram
external usenet poster
 
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  
Old June 27th, 2005, 09:02 PM
thephoenix12
external usenet poster
 
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access Mail Merge to Word.doc files ? RNUSZ@OKDPS Setting Up & Running Reports 1 May 18th, 2005 06:31 PM
Need Help In Printing Current Record in Specific Report RNUSZ@OKDPS Setting Up & Running Reports 1 May 16th, 2005 09:06 PM
Open & Read Newly created Table for Reporting ? RNUSZ@OKDPS Setting Up & Running Reports 0 May 9th, 2005 03:43 PM
Sorry guyz.... jim0861 Powerpoint 3 April 15th, 2005 03:39 PM
MsgBox Displays Too Early gdtatuiowa General Discussion 7 October 1st, 2004 10:06 PM


All times are GMT +1. The time now is 03:23 PM.


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