Good morning,

I am trying to find a way to search a spreadsheet that contains several
hundreds of thousands of dates. Searching isn't really the problem, reporting
however is the issue.

What I would like is, to be able to search all of the cells in a worksheet
for a letter such as €œm€� and then automatically produce a report (worksheet)
that contains all of the values that match that criteria as well as
referencing the header/column letter where the cell was found.

Thank you for your help,

Fred

Create a worksheet Summary and change the worksheet name that you are
searching in the code below


Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
RowCount = RowCount + 1
End With
Set c = .FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


"fgwiii" wrote:

Good morning,

I am trying to find a way to search a spreadsheet that contains several
hundreds of thousands of dates. Searching isn't really the problem, reporting
however is the issue.

What I would like is, to be able to search all of the cells in a worksheet
for a letter such as €œm€� and then automatically produce a report (worksheet)
that contains all of the values that match that criteria as well as
referencing the header/column letter where the cell was found.

Thank you for your help,

Fred

Hello,

When I attempt to run this, I get an Run-time error'9': Subscript out of
range.

Not being sure what to do, I clicked on sheet 2 and then right clicked, view
code, and then pasted the code and ran it (as a macro).

Thanks for your help!

Fred

"Joel" wrote:

Create a worksheet Summary and change the worksheet name that you are
searching in the code below


Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
RowCount = RowCount + 1
End With
Set c = .FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


"fgwiii" wrote:

Good morning,

I am trying to find a way to search a spreadsheet that contains several
hundreds of thousands of dates. Searching isn't really the problem, reporting
however is the issue.

What I would like is, to be able to search all of the cells in a worksheet
for a letter such as €œm€� and then automatically produce a report (worksheet)
that contains all of the values that match that criteria as well as
referencing the header/column letter where the cell was found.

Thank you for your help,

Fred

You need to make two changes

1) change this line to match the sheet name you are working with

With Sheets("Sheet1")

2) Add a worksheet named SUMMARY to your workbook.


"fgwiii" wrote:

Hello,

When I attempt to run this, I get an Run-time error'9': Subscript out of
range.

Not being sure what to do, I clicked on sheet 2 and then right clicked, view
code, and then pasted the code and ran it (as a macro).

Thanks for your help!

Fred

"Joel" wrote:

Create a worksheet Summary and change the worksheet name that you are
searching in the code below


Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
RowCount = RowCount + 1
End With
Set c = .FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


"fgwiii" wrote:

Good morning,

I am trying to find a way to search a spreadsheet that contains several
hundreds of thousands of dates. Searching isn't really the problem, reporting
however is the issue.

What I would like is, to be able to search all of the cells in a worksheet
for a letter such as €œm€� and then automatically produce a report (worksheet)
that contains all of the values that match that criteria as well as
referencing the header/column letter where the cell was found.

Thank you for your help,

Fred

Hello,

Sorry to be a pain, but I have made the changes but I am now getting a new
error:
Run-time error '438':
object doesn't support this property or method.

Just as an FYI - The date in my sheet covers columns A1 to R1 all the way
down to row 30134.

Thank you,

Fred

"Joel" wrote:

You need to make two changes

1) change this line to match the sheet name you are working with

With Sheets("Sheet1")

2) Add a worksheet named SUMMARY to your workbook.


"fgwiii" wrote:

Hello,

When I attempt to run this, I get an Run-time error'9': Subscript out of
range.

Not being sure what to do, I clicked on sheet 2 and then right clicked, view
code, and then pasted the code and ran it (as a macro).

Thanks for your help!

Fred

"Joel" wrote:

Create a worksheet Summary and change the worksheet name that you are
searching in the code below


Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
RowCount = RowCount + 1
End With
Set c = .FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


"fgwiii" wrote:

Good morning,

I am trying to find a way to search a spreadsheet that contains several
hundreds of thousands of dates. Searching isn't really the problem, reporting
however is the issue.

What I would like is, to be able to search all of the cells in a worksheet
for a letter such as €œm€� and then automatically produce a report (worksheet)
that contains all of the values that match that criteria as well as
referencing the header/column letter where the cell was found.

Thank you for your help,

Fred

from
Set c = .FindNext(c)
to
Set c = .Cells.FindNext(c)

"fgwiii" wrote:

Hello,

Sorry to be a pain, but I have made the changes but I am now getting a new
error:
Run-time error '438':
object doesn't support this property or method.

Just as an FYI - The date in my sheet covers columns A1 to R1 all the way
down to row 30134.

Thank you,

Fred

"Joel" wrote:

You need to make two changes

1) change this line to match the sheet name you are working with

With Sheets("Sheet1")

2) Add a worksheet named SUMMARY to your workbook.


"fgwiii" wrote:

Hello,

When I attempt to run this, I get an Run-time error'9': Subscript out of
range.

Not being sure what to do, I clicked on sheet 2 and then right clicked, view
code, and then pasted the code and ran it (as a macro).

Thanks for your help!

Fred

"Joel" wrote:

Create a worksheet Summary and change the worksheet name that you are
searching in the code below


Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
RowCount = RowCount + 1
End With
Set c = .FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


"fgwiii" wrote:

Good morning,

I am trying to find a way to search a spreadsheet that contains several
hundreds of thousands of dates. Searching isn't really the problem, reporting
however is the issue.

What I would like is, to be able to search all of the cells in a worksheet
for a letter such as €œm€� and then automatically produce a report (worksheet)
that contains all of the values that match that criteria as well as
referencing the header/column letter where the cell was found.

Thank you for your help,

Fred

I was not sure were to put the code, but I tried it in several places and
each time I received errors or it would not compile.

Thanks

Fred

"Joel" wrote:

from
Set c = .FindNext(c)
to
Set c = .Cells.FindNext(c)

"fgwiii" wrote:

Hello,

Sorry to be a pain, but I have made the changes but I am now getting a new
error:
Run-time error '438':
object doesn't support this property or method.

Just as an FYI - The date in my sheet covers columns A1 to R1 all the way
down to row 30134.

Thank you,

Fred

"Joel" wrote:

You need to make two changes

1) change this line to match the sheet name you are working with

With Sheets("Sheet1")

2) Add a worksheet named SUMMARY to your workbook.


"fgwiii" wrote:

Hello,

When I attempt to run this, I get an Run-time error'9': Subscript out of
range.

Not being sure what to do, I clicked on sheet 2 and then right clicked, view
code, and then pasted the code and ran it (as a macro).

Thanks for your help!

Fred

"Joel" wrote:

Create a worksheet Summary and change the worksheet name that you are
searching in the code below


Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
RowCount = RowCount + 1
End With
Set c = .FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


"fgwiii" wrote:

Good morning,

I am trying to find a way to search a spreadsheet that contains several
hundreds of thousands of dates. Searching isn't really the problem, reporting
however is the issue.

What I would like is, to be able to search all of the cells in a worksheet
for a letter such as €œm€� and then automatically produce a report (worksheet)
that contains all of the values that match that criteria as well as
referencing the header/column letter where the cell was found.

Thank you for your help,

Fred

Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
End With
Set c = .Cells.FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


"fgwiii" wrote:

I was not sure were to put the code, but I tried it in several places and
each time I received errors or it would not compile.

Thanks

Fred

"Joel" wrote:

from
Set c = .FindNext(c)
to
Set c = .Cells.FindNext(c)

"fgwiii" wrote:

Hello,

Sorry to be a pain, but I have made the changes but I am now getting a new
error:
Run-time error '438':
object doesn't support this property or method.

Just as an FYI - The date in my sheet covers columns A1 to R1 all the way
down to row 30134.

Thank you,

Fred

"Joel" wrote:

You need to make two changes

1) change this line to match the sheet name you are working with

With Sheets("Sheet1")

2) Add a worksheet named SUMMARY to your workbook.


"fgwiii" wrote:

Hello,

When I attempt to run this, I get an Run-time error'9': Subscript out of
range.

Not being sure what to do, I clicked on sheet 2 and then right clicked, view
code, and then pasted the code and ran it (as a macro).

Thanks for your help!

Fred

"Joel" wrote:

Create a worksheet Summary and change the worksheet name that you are
searching in the code below


Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
RowCount = RowCount + 1
End With
Set c = .FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


"fgwiii" wrote:

Good morning,

I am trying to find a way to search a spreadsheet that contains several
hundreds of thousands of dates. Searching isn't really the problem, reporting
however is the issue.

What I would like is, to be able to search all of the cells in a worksheet
for a letter such as €œm€� and then automatically produce a report (worksheet)
that contains all of the values that match that criteria as well as
referencing the header/column letter where the cell was found.

Thank you for your help,

Fred

Okay we are getting somewhere!

When I run the code and while watching the Summary page I see several header
rows appear in cell A1 but only for a second until it is done running.

Cell a1 contains "EXAM_DT_FUL" amd cell a2 contains "$S$1"

Is it possible for not only the header rows, but actual data to be listed in
the summary sheet?

Thanks,

Fred

"Joel" wrote:

Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
End With
Set c = .Cells.FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


"fgwiii" wrote:

I was not sure were to put the code, but I tried it in several places and
each time I received errors or it would not compile.

Thanks

Fred

"Joel" wrote:

from
Set c = .FindNext(c)
to
Set c = .Cells.FindNext(c)

"fgwiii" wrote:

Hello,

Sorry to be a pain, but I have made the changes but I am now getting a new
error:
Run-time error '438':
object doesn't support this property or method.

Just as an FYI - The date in my sheet covers columns A1 to R1 all the way
down to row 30134.

Thank you,

Fred

"Joel" wrote:

You need to make two changes

1) change this line to match the sheet name you are working with

With Sheets("Sheet1")

2) Add a worksheet named SUMMARY to your workbook.


"fgwiii" wrote:

Hello,

When I attempt to run this, I get an Run-time error'9': Subscript out of
range.

Not being sure what to do, I clicked on sheet 2 and then right clicked, view
code, and then pasted the code and ran it (as a macro).

Thanks for your help!

Fred

"Joel" wrote:

Create a worksheet Summary and change the worksheet name that you are
searching in the code below


Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
RowCount = RowCount + 1
End With
Set c = .FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


"fgwiii" wrote:

Good morning,

I am trying to find a way to search a spreadsheet that contains several
hundreds of thousands of dates. Searching isn't really the problem, reporting
however is the issue.

What I would like is, to be able to search all of the cells in a worksheet
for a letter such as €œm€� and then automatically produce a report (worksheet)
that contains all of the values that match that criteria as well as
referencing the header/column letter where the cell was found.

Thank you for your help,

Fred

Yes. Where does the data appear with respect to the header row. Like in the
next Row colum D. Is the Data more than one Row. ? Does the number of Rows
end with a Blank Row?

"fgwiii" wrote:

Okay we are getting somewhere!

When I run the code and while watching the Summary page I see several header
rows appear in cell A1 but only for a second until it is done running.

Cell a1 contains "EXAM_DT_FUL" amd cell a2 contains "$S$1"

Is it possible for not only the header rows, but actual data to be listed in
the summary sheet?

Thanks,

Fred

"Joel" wrote:

Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
End With
Set c = .Cells.FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


"fgwiii" wrote:

I was not sure were to put the code, but I tried it in several places and
each time I received errors or it would not compile.

Thanks

Fred

"Joel" wrote:

from
Set c = .FindNext(c)
to
Set c = .Cells.FindNext(c)

"fgwiii" wrote:

Hello,

Sorry to be a pain, but I have made the changes but I am now getting a new
error:
Run-time error '438':
object doesn't support this property or method.

Just as an FYI - The date in my sheet covers columns A1 to R1 all the way
down to row 30134.

Thank you,

Fred

"Joel" wrote:

You need to make two changes

1) change this line to match the sheet name you are working with

With Sheets("Sheet1")

2) Add a worksheet named SUMMARY to your workbook.


"fgwiii" wrote:

Hello,

When I attempt to run this, I get an Run-time error'9': Subscript out of
range.

Not being sure what to do, I clicked on sheet 2 and then right clicked, view
code, and then pasted the code and ran it (as a macro).

Thanks for your help!

Fred

"Joel" wrote:

Create a worksheet Summary and change the worksheet name that you are
searching in the code below


Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
RowCount = RowCount + 1
End With
Set c = .FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


"fgwiii" wrote:

Good morning,

I am trying to find a way to search a spreadsheet that contains several
hundreds of thousands of dates. Searching isn't really the problem, reporting
however is the issue.

What I would like is, to be able to search all of the cells in a worksheet
for a letter such as €œm€� and then automatically produce a report (worksheet)
that contains all of the values that match that criteria as well as
referencing the header/column letter where the cell was found.

Thank you for your help,

Fred

Somehow an instruction is missing in the code

Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
RowCount = RowCount + 1
End With
Set c = .Cells.FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub



"fgwiii" wrote:

Okay we are getting somewhere!

When I run the code and while watching the Summary page I see several header
rows appear in cell A1 but only for a second until it is done running.

Cell a1 contains "EXAM_DT_FUL" amd cell a2 contains "$S$1"

Is it possible for not only the header rows, but actual data to be listed in
the summary sheet?

Thanks,

Fred

"Joel" wrote:

Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
End With
Set c = .Cells.FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


"fgwiii" wrote:

I was not sure were to put the code, but I tried it in several places and
each time I received errors or it would not compile.

Thanks

Fred

"Joel" wrote:

from
Set c = .FindNext(c)
to
Set c = .Cells.FindNext(c)

"fgwiii" wrote:

Hello,

Sorry to be a pain, but I have made the changes but I am now getting a new
error:
Run-time error '438':
object doesn't support this property or method.

Just as an FYI - The date in my sheet covers columns A1 to R1 all the way
down to row 30134.

Thank you,

Fred

"Joel" wrote:

You need to make two changes

1) change this line to match the sheet name you are working with

With Sheets("Sheet1")

2) Add a worksheet named SUMMARY to your workbook.


"fgwiii" wrote:

Hello,

When I attempt to run this, I get an Run-time error'9': Subscript out of
range.

Not being sure what to do, I clicked on sheet 2 and then right clicked, view
code, and then pasted the code and ran it (as a macro).

Thanks for your help!

Fred

"Joel" wrote:

Create a worksheet Summary and change the worksheet name that you are
searching in the code below


Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
RowCount = RowCount + 1
End With
Set c = .FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


"fgwiii" wrote:

Good morning,

I am trying to find a way to search a spreadsheet that contains several
hundreds of thousands of dates. Searching isn't really the problem, reporting
however is the issue.

What I would like is, to be able to search all of the cells in a worksheet
for a letter such as €œm€� and then automatically produce a report (worksheet)
that contains all of the values that match that criteria as well as
referencing the header/column letter where the cell was found.

Thank you for your help,

Fred

Joel,

Thank you very much - that did the trick!

Your help is very much appreciated!

Best regards

Fred

"Joel" wrote:

Somehow an instruction is missing in the code

Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
RowCount = RowCount + 1
End With
Set c = .Cells.FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub



"fgwiii" wrote:

Okay we are getting somewhere!

When I run the code and while watching the Summary page I see several header
rows appear in cell A1 but only for a second until it is done running.

Cell a1 contains "EXAM_DT_FUL" amd cell a2 contains "$S$1"

Is it possible for not only the header rows, but actual data to be listed in
the summary sheet?

Thanks,

Fred

"Joel" wrote:

Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
End With
Set c = .Cells.FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


"fgwiii" wrote:

I was not sure were to put the code, but I tried it in several places and
each time I received errors or it would not compile.

Thanks

Fred

"Joel" wrote:

from
Set c = .FindNext(c)
to
Set c = .Cells.FindNext(c)

"fgwiii" wrote:

Hello,

Sorry to be a pain, but I have made the changes but I am now getting a new
error:
Run-time error '438':
object doesn't support this property or method.

Just as an FYI - The date in my sheet covers columns A1 to R1 all the way
down to row 30134.

Thank you,

Fred

"Joel" wrote:

You need to make two changes

1) change this line to match the sheet name you are working with

With Sheets("Sheet1")

2) Add a worksheet named SUMMARY to your workbook.


"fgwiii" wrote:

Hello,

When I attempt to run this, I get an Run-time error'9': Subscript out of
range.

Not being sure what to do, I clicked on sheet 2 and then right clicked, view
code, and then pasted the code and ran it (as a macro).

Thanks for your help!

Fred

"Joel" wrote:

Create a worksheet Summary and change the worksheet name that you are
searching in the code below


Sub MakeReport()

RowCount = 1
With Sheets("Sheet1")
Set c = .Cells.Find(what:="m", LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Sheets("Summary")
.Range("A" & RowCount) = c.Value
.Range("B" & RowCount) = c.Address
RowCount = RowCount + 1
End With
Set c = .FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

End Sub


"fgwiii" wrote:

Good morning,

I am trying to find a way to search a spreadsheet that contains several
hundreds of thousands of dates. Searching isn't really the problem, reporting
however is the issue.

What I would like is, to be able to search all of the cells in a worksheet
for a letter such as €œm€� and then automatically produce a report (worksheet)
that contains all of the values that match that criteria as well as
referencing the header/column letter where the cell was found.

Thank you for your help,

Fred