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 |
#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 D911, 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 |
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 |