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. |
|
|
Thread Tools | Display Modes |
#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 D930, 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 |
Thread Tools | |
Display Modes | |
|
|
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 |