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