alternative to msgbox

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

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

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

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

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

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

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

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

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

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