Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Msgbox saying "Unable to read file" | Excel Discussion (Misc queries) | |||
Seeking an alternative to blank ("") cell in IF arguments | Excel Discussion (Misc queries) | |||
Msgbox based on If statement | Excel Worksheet Functions | |||
Msgbox | Excel Worksheet Functions | |||
Help with MsgBox... | Excel Worksheet Functions |