More commentary in the code. See double-apostrophe comments, as before.

Sub extractall()
' this procedure uses the "criteria file" created above to
' extract the full set of data from the "source data" file

'' The call that precedes this is noduplicaterows,
'' so the Selection is this - where noduplicaterows leaves off - I believe.
''
'' Sheets("Sheet1").Select
'' Sheets("Sheet1").Name = "criteria file"
'' Range("A1").Select
''
'' This routine would be so much more readable if this it used
'' Sheets("criteria file").Range("A1") or whatever is appropriate
'' instead of Selection.

Sheets("source data").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Range("A1:I53263").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Sheets("criteria file").Range("A1:F7"), _
Unique:=False
ActiveWindow.ScrollColumn = 5 ''necessary?
ActiveWindow.ScrollColumn = 1 ''necessary?

'' Not sure of the effect of the filter on the selection.
'' If nothing, then the selection is all the contiguous data,
'' starting at cell A1.
Selection.Copy

'' It looks to me that you know that Sheet2 happens to be the
'' added sheet, but this is not necessarily the case in an
'' arbitrary workbook.
''
'' Worksheets.Add
'' Sheets("sheet2").Name = "final data"
'' Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
'' SkipBlanks:=False, Transpose:=False

'' Better:
'' Get a specific reference to the added sheet.
Dim wks As Worksheet
Set wks = Worksheets.Add
'name it
wks.Name = "final data"
'paste data in it
wks.Cells(1).PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'' This section looks like it is copying and pasting the same data
'' that was just added since Cells always refers to the active sheet.
'' If so, it can be omitted.
''
'' Cells.Select
'' Application.CutCopyMode = False
'' Selection.Copy
'' Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
'' SkipBlanks:=False, Transpose:=False

Set wkb = Nothing 'done with this object

End Sub

Sub codedata()
'this procedure codes all the data rows in the file "final
'data ". I have used a combination cacatenation and vlookup"
'technique to do the coding in mass. But actually, would it be
'possible to redo this section so that it cacatenates, vlookups
'and codes one Row at a time? For each row, I`d also like to be
'able to check one row at a time whether there is an id code
'for this row or not. And to put the rows which are id coded
'on one sheet and those for which there is no id code on a
'separate sheet.

Dim rng As Range, rng2 As Range
Dim rng1 As Range, cell As Range
Dim sStr As String, sStr1 As String

'prepare final data worksheet for coding
'' rather than...
'' Sheets("final data").Select
'' Cells.Select
'' Application.CutCopyMode = False
'' Selection.Copy
'' Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
'' SkipBlanks:=False, Transpose:=False

'' I'd prefer...
Sheets("final data").Cells.Copy
Sheets("final data").Cells(1).PasteSpecial _
Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
'' less ambiguous.

'' Isn't AcitiveCell A1 of Sheets("final data")?
''
Set rng = Cells(ActiveCell.Row, "IV").End(xlToLeft)
Set rng1 = Range(ActiveCell, Cells(Rows.Count, _
ActiveCell.Column).End(xlUp))

ActiveCell.EntireColumn.Insert
Set rng2 = Range(rng1(1), rng)

Debug.Print rng2.Address

- - -
David,
I am losing it here. W/o the actual workbook, it is hard for me to be sure
what cell is active or what the selection is, et al. But, no thanks. I don't
want the actual workbook. Perhaps my comments have been helpful. If you
tighten up this code, perhaps it will become clearer to you or others how
best to optimize it.

Regards,
Bob

"david shapiro" wrote in message
...
I've put together this code to extract and id code data from an excel
workbook, but have come across quite a few bugs. I've tried to put
comments in the VB code describing what's going on at each stage. I
would appreciate it if someone could clear up the errors and get it
running. Thanks.

<balance of message deleted for brevity. See original post.

Bob,

Thanks for the suggestions on the code in the macro. I am in the
process of trying them out and tightening the code.

That last part in the macro - sub codedata - this is what it's doing:

It takes the dataset in the worksheet "final data" which has several
columns which have various headings (this worksheet had been created in
the last sub). A new column is added as the first column and given 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. How do you suggest is
the best way to do this?

Regards,
Dave


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

Dave,
Is it the indicator ID code that you want in the long run, or the data in
the source that the indicator ID code represents? Is the indicator ID code
an end in itself, or a means to an end?

Putting aside how to achieve the goal, what is the goal of Sub codedata? Are
you saying that the goal is to separate the data in "source data" into data
that matches "reference" and data that does not?

Putting aside how you would implement it in code, how would you describe the
process in human terms? In other words, what instructions would you give to
a human helper so they could do manually what you want to accomplish?

Am I understanding correctly that "source data," "reference" and "final
data" have essentially the same format?

Bob


"david shapiro" wrote in message
...
Bob,

Thanks for the suggestions on the code in the macro. I am in the
process of trying them out and tightening the code.

That last part in the macro - sub codedata - this is what it's doing:

It takes the dataset in the worksheet "final data" which has several
columns which have various headings (this worksheet had been created in
the last sub). A new column is added as the first column and given 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. How do you suggest is
the best way to do this?

Regards,
Dave

Bob,

The goal of sub codedata is to take the "final data" worksheet, add a
column and to put in this column the correct id code for every row in
the dataset.

You can disregard the "source data" worksheet as it no longer applies
for sub codedata. An example of the format for the "final data"
worksheet is data in columns running across with these headings:
country year source datatype subgroup gender measurement value
Canada 1980 ILO census age 15-19 men number 104.4

The goal is to add the id code to the above for every row in the dataset
such as:

id code country year source datatype subgroup gender measurement value
11771 Canada 1980 ILO census age 15-19 men number 104.4

The "reference" worksheet is data in columns with similar headings and
info:
id no. source datatype subgroup gender measurement

When the source, datatype, subgroup, gender and measurement match
exactly for the row in "final data" and in "reference", the "reference"
sheet provides the id no. for this indicator.

This is the id code I'd like to put in the first column of "final data"
for every row of data.

It would be good if the final product of the macro is the following:

1) the "final data" worksheet with the following addition to the data:
an indicator id column has been inserted and all the rows now have the
correct indicator id number. The vlookup/cacatenation technique is just
one way I thought the coding could be done, but if there is another more
effective way to do the coding, feel free to apply that.

2) a new created worksheet called "id codes missing" which contains a
list of all the rows of data for which an id code could not be found.
(The "final data" worksheet at the end should only contain those rows
for which an id code could be found, and all those for which the code
could not be found are in this sheet).

Hope I've described it clearly. Just let me know if you need more
clarification.

Dave






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