#1   Report Post  
thephoenix12
 
Posts: n/a
Default macro help


Hi,
I have a macro that I am trying to edit, but I am not having much luck.
Right now, when the macro runs, it searches through a selection of
worksheets, outputs the name of each worksheet, and then lists the
cells that are not blank, in a specified range. So the output looks
like this

Worksheet 1
Cell 2
Cell 5

Worksheet 2
Cell 1

Worksheet 3
Cell 3
Cell 4
Cell 6

The range that is selected to be searched through is one column, for
example, D9:D65. What I am trying to do, is have the range be multiple
columns, for example, D9:F65, and then have the macro output each column
under its worksheet name, like this. (this is considering there was a
two column range)

Worksheet 1

Cell 2
Cell 5

Worksheet 1

Cell 3
Cell 5

Worksheet 2

Cell 1
Cell 3

Worksheet 2

Cell 2
Cell 3

What I have written so far gives me an output looking something like
that, but it only searches through one column of cells. So instead it
looks like this:

Worksheet 1

Cell 2
Cell 5

Worksheet 1

Cell 2
Cell 5

Worksheet 2

Cell 1
Cell 3

Worksheet 2

Cell 1
Cell 3

I do not know how to make it search through one column the first time,
then move to another column the next time, and so on. If anyone has
any ideas that would be a big help. Here is my code so far:

Sub PeopleSearch()
Dim W As Worksheet
Dim range_input, raange_input, b_range, e_range As Range
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VALU = InputBox("Enter a week range (number of columns to be searched
through, ex. D7:F7)")
Set raange_input = Range(VALU)
VAL = InputBox("Enter which range to search in: (ex. D9:D62)")
Set range_input = Range(VAL)

For Each W In Worksheets
W.Select
For Each b_range In raange_input
If W.Name < "Summary" Then
temp = temp & Chr(10) & W.Name & Chr(10)
End If
For Each e_range In range_input
If W.Name < sh_skip Then

If Trim(W.Range(e_range.Address).Value) < "" And
Trim(W.Range(e_range.Address).Value) < "0" Then
temp = temp & W.Range("b" & e_range.Row).Value & Chr(10)

End If
End If
Next
Next
Next

Workbooks.Add
Range("a1").Select
Selection.Value = "CURRENT PROJECTS LIST"
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 Or Not only_text(ActiveCell) Or
ActiveCell.Value = "RM UCONN SFA" Then
ActiveCell.Font.Bold = True
End If
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"file", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="file"
End Sub


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=386890

  #2   Report Post  
moi
 
Posts: n/a
Default

http://www.geocities.com/smplprgrsrc...heckRanges.zip checks each sheet
in a workbook for values and lists them.

"thephoenix12"
schreef in bericht
news:thephoenix12.1s4e08_1121278075.0787@excelforu m-nospam.com...

Hi,
I have a macro that I am trying to edit, but I am not having much luck.
Right now, when the macro runs, it searches through a selection of
worksheets, outputs the name of each worksheet, and then lists the
cells that are not blank, in a specified range. So the output looks
like this

Worksheet 1
Cell 2
Cell 5

Worksheet 2
Cell 1

Worksheet 3
Cell 3
Cell 4
Cell 6

The range that is selected to be searched through is one column, for
example, D9:D65. What I am trying to do, is have the range be multiple
columns, for example, D9:F65, and then have the macro output each column
under its worksheet name, like this. (this is considering there was a
two column range)

Worksheet 1

Cell 2
Cell 5

Worksheet 1

Cell 3
Cell 5

Worksheet 2

Cell 1
Cell 3

Worksheet 2

Cell 2
Cell 3

What I have written so far gives me an output looking something like
that, but it only searches through one column of cells. So instead it
looks like this:

Worksheet 1

Cell 2
Cell 5

Worksheet 1

Cell 2
Cell 5

Worksheet 2

Cell 1
Cell 3

Worksheet 2

Cell 1
Cell 3

I do not know how to make it search through one column the first time,
then move to another column the next time, and so on. If anyone has
any ideas that would be a big help. Here is my code so far:

Sub PeopleSearch()
Dim W As Worksheet
Dim range_input, raange_input, b_range, e_range As Range
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VALU = InputBox("Enter a week range (number of columns to be searched
through, ex. D7:F7)")
Set raange_input = Range(VALU)
VAL = InputBox("Enter which range to search in: (ex. D9:D62)")
Set range_input = Range(VAL)

For Each W In Worksheets
W.Select
For Each b_range In raange_input
If W.Name < "Summary" Then
temp = temp & Chr(10) & W.Name & Chr(10)
End If
For Each e_range In range_input
If W.Name < sh_skip Then

If Trim(W.Range(e_range.Address).Value) < "" And
Trim(W.Range(e_range.Address).Value) < "0" Then
temp = temp & W.Range("b" & e_range.Row).Value & Chr(10)

End If
End If
Next
Next
Next

Workbooks.Add
Range("a1").Select
Selection.Value = "CURRENT PROJECTS LIST"
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 Or Not only_text(ActiveCell) Or
ActiveCell.Value = "RM UCONN SFA" Then
ActiveCell.Font.Bold = True
End If
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"file", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="file"
End Sub


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile:
http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=386890



  #3   Report Post  
Dave Peterson
 
Posts: n/a
Default

I think I would just show the worksheet name and the addresses of the filled in
cells--I'm not sure I know what cell 2 is D9:F65. Would it be D10 or F9?

Anyway, I think that this may do what you want (with a twist)...


Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim oRow As Long
Dim curWkbk As Workbook

Dim SummaryWks As Worksheet
Dim wks As Worksheet

Set curWkbk = ActiveWorkbook

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox("Please select a range to search." & _
vbLf & "The address will be used on all sheets.", Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub 'user hit cancel
End If

Set SummaryWks = Workbooks.Add(1).Worksheets(1)
With SummaryWks.Range("a1")
.Value = "CURRENT PROJECTS LIST -- Inspecting Range: " _
& myRng.Address(0, 0)
.Font.Bold = True
End With

oRow = 0
For Each wks In curWkbk.Worksheets
oRow = oRow + 2
With SummaryWks.Cells(oRow, "A")
.Value = "'" & wks.Name
.Font.Bold = True
End With
For Each myCell In wks.Range(myRng.Address).Cells
If IsEmpty(myCell) Then
'do nothing
ElseIf IsNumeric(myCell.Value) Then
If myCell.Value < 0 Then
oRow = oRow + 1
SummaryWks.Cells(oRow, "A").Value = myCell.Address(0, 0)
End If
End If
Next myCell
Next wks

End Sub




thephoenix12 wrote:

Hi,
I have a macro that I am trying to edit, but I am not having much luck.
Right now, when the macro runs, it searches through a selection of
worksheets, outputs the name of each worksheet, and then lists the
cells that are not blank, in a specified range. So the output looks
like this

Worksheet 1
Cell 2
Cell 5

Worksheet 2
Cell 1

Worksheet 3
Cell 3
Cell 4
Cell 6

The range that is selected to be searched through is one column, for
example, D9:D65. What I am trying to do, is have the range be multiple
columns, for example, D9:F65, and then have the macro output each column
under its worksheet name, like this. (this is considering there was a
two column range)

Worksheet 1

Cell 2
Cell 5

Worksheet 1

Cell 3
Cell 5

Worksheet 2

Cell 1
Cell 3

Worksheet 2

Cell 2
Cell 3

What I have written so far gives me an output looking something like
that, but it only searches through one column of cells. So instead it
looks like this:

Worksheet 1

Cell 2
Cell 5

Worksheet 1

Cell 2
Cell 5

Worksheet 2

Cell 1
Cell 3

Worksheet 2

Cell 1
Cell 3

I do not know how to make it search through one column the first time,
then move to another column the next time, and so on. If anyone has
any ideas that would be a big help. Here is my code so far:

Sub PeopleSearch()
Dim W As Worksheet
Dim range_input, raange_input, b_range, e_range As Range
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VALU = InputBox("Enter a week range (number of columns to be searched
through, ex. D7:F7)")
Set raange_input = Range(VALU)
VAL = InputBox("Enter which range to search in: (ex. D9:D62)")
Set range_input = Range(VAL)

For Each W In Worksheets
W.Select
For Each b_range In raange_input
If W.Name < "Summary" Then
temp = temp & Chr(10) & W.Name & Chr(10)
End If
For Each e_range In range_input
If W.Name < sh_skip Then

If Trim(W.Range(e_range.Address).Value) < "" And
Trim(W.Range(e_range.Address).Value) < "0" Then
temp = temp & W.Range("b" & e_range.Row).Value & Chr(10)

End If
End If
Next
Next
Next

Workbooks.Add
Range("a1").Select
Selection.Value = "CURRENT PROJECTS LIST"
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 Or Not only_text(ActiveCell) Or
ActiveCell.Value = "RM UCONN SFA" Then
ActiveCell.Font.Bold = True
End If
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"file", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="file"
End Sub

--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=386890


--

Dave Peterson
  #4   Report Post  
anilsolipuram
 
Posts: n/a
Default


Try this macro and let me know


Sub PeopleSearch()
Dim W As Worksheet
Dim range_input, raange_input, b_range, e_range As Range
Dim VAL, sh_skip, temp, counter As Variant
sh_skip = "Summary" 'sheetname to skip
VALU = InputBox("Enter a week range (number of columns to be searched
through, ex. D7:F7)")
Set raange_input = Range(VALU)
VAL = InputBox("Enter which range to search in: (ex. D9:D62)")
Set range_input = Range(VAL)

For Each W In Worksheets
W.Select
counter = 0
For Each b_range In raange_input

If W.Name < "Summary" Then
temp = temp & Chr(10) & W.Name & Chr(10)
End If
For Each e_range In range_input
If W.Name < sh_skip Then
If counter = 0 Then
If Trim(W.Range(e_range.Address).Value) < "" And
Trim(W.Range(e_range.Address).Value) < "0" Then
temp = temp & W.Range("b" & e_range.Row).Value & Chr(10)
End If
Else
If Trim(W.Range(e_range.Address).Offset(counter, 0).Value) < ""
And Trim(W.Range(e_range.Address).Offset(counter, 0).Value) < "0"
Then
temp = temp & W.Range("b" & e_range.Row).Value & Chr(10)
End If
End If

End If
Next
counter = counter + 1
Next
Next

Workbooks.Add
Range("a1").Select
Selection.Value = "CURRENT PROJECTS LIST"
Selection.Font.Bold = True
temp1 = Split(temp, Chr(10))
Range("a2").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
If Len(ActiveCell) 13 Or Not only_text(ActiveCell) Or
ActiveCell.Value = "RM UCONN SFA" Then
ActiveCell.Font.Bold = True
End If
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"file", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="file"
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=386890

  #5   Report Post  
thephoenix12
 
Posts: n/a
Default


Yes that works; I just had to switch the 0 and counter positions.
Thanks so much!

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=386890

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with macro looping and color query function kevinm Excel Discussion (Misc queries) 10 May 26th 05 01:25 AM
Playing a macro from another workbook Jim Excel Discussion (Misc queries) 1 February 23rd 05 11:12 PM
Date macro Hiking Excel Discussion (Misc queries) 9 February 3rd 05 01:40 AM
Macro and If Statement SATB Excel Discussion (Misc queries) 2 December 3rd 04 05:46 PM
Macro Formula revision? Mark Excel Worksheet Functions 1 November 28th 04 02:43 AM


All times are GMT +1. The time now is 02:40 PM.

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

About Us

"It's about Microsoft Excel"