First, I didn't look at your code.

But for this question, I'd look for those values:

Dim FoundYearCell As Range
Dim FoundZZZCell As Range
Dim wks As Worksheet
Dim myRng As Range

Set wks = Worksheets("sheet1") 'whatever??

With wks.Cells
Set FoundYearCell = .Find(what:="Year", after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

Set FoundZZZCell = .Find(what:="zzz", after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If FoundYearCell Is Nothing _
Or FoundZZZCell Is Nothing Then
MsgBox "Something's missing"
Else
Set myRng = .Range(.Cells(FoundZZZCell.Row, FoundYearCell.Column), _
.Cells(.Rows.Count, FoundYearCell.Column).End(xlUp))
End If

(You may want to go back to your thread with Bob Kilmer. He seems to have it
well in hand.)

david shapiro wrote:

Dave,

Just had a question referring back to the original code in the line:

Set myRng = .Range("d6:g" & _
.Cells(.Rows.Count, "A").End(xlUp).Row)

Is the d6 referring to the first cell where the prgramme starts looking
for red cells.

How is the best way to handle this if this cell varies from worksheet to
worksheet. But it is always the cell at the intersection of the column
which contains the words "year" somewhere in the worksheet and the row
which contains the word "ZZZ" somewhere in the worksheet. How would be
the best way to do the above range line, taking this into consideration?

Don't mean to bring this up again, but if you have a chance, am
wondering if you might know how to go about the last part of the macro
code I had sent:

Take the dataset in the worksheet "final data" which has several columns
which have various headings. Add new column as the first column and
give it the heading "indicator id". The objective is to find the
indicator id code there for the data in the row, to loop through and do
this row by row until the end of the dataset.

The indicator ID code can be found in the "reference" worksheet. The
correct indicator id code in the "reference" file is the one for which
the data row in the worksheets "final data" and "reference" shares the
same contents in the columns headed by:
indicator, subgroup, gender and measurement.

I have thought one possible way might be to cacatenate the indicator,
subgroup, gender and measurement columns in both the "source data" and
"reference" worksheets, compare them using a vlookup to find the correct
indicator id code in the "reference" worksheet, and then put that
indicator id code in the created blank column (1st column) in the "final
data" worksheet. And to loop through so it does this for all the rows
one at a time. it would be good too if all the rows for which an
indicator id code could not be found in the "reference" worksheet are
put in a separate newly created worksheet page.

Some suggestions on this would be appreciated.

Regards,
Dave



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

--

Dave Peterson