Find and report
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
|