See bottom of message.

"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.

Dave Shapiro

Option Explicit
Sub extraction_codingmacro()

Dim wks As Worksheet
Dim SumWks As Worksheet
Dim myCell As Range
Dim oRow As Long
Dim myRng As Range

Set SumWks = Worksheets.Add
SumWks.Range("a1").Resize(1, 7).Value _
= Array("country", "source", "indicator", "data type",
"subgroup", "year", "value")

oRow = 1
For Each wks In ActiveWorkbook.Worksheets
If wks.Name = SumWks.Name Or wks.Name = "criteria file" Or _
wks.Name = "reference" Then
'do nothing
Else
wks.Select
Call preparefile
With wks
Set myRng = .Range("d8:aa" & _
.Cells(.Rows.Count, "A").End(xlUp).Row)
End With
With SumWks
For Each myCell In myRng.Cells
If myCell.Interior.ColorIndex = 3 Then

'the next two lines are supposed to filter out
all
the rows with the words
' "GSD" in the B column of the row and rows
with
the
words "AAA" in the D column
' of the row. But this doesn`t seem to work.
Could
you adjust this?

'If myCell.Cells(myCell.Row, "B").Text <
"GSD"
Then
'If InStr(1, myCell.Cells(myCell.Row,
"D").Text,"AAA", vbTextCompare) = 0
'Then

<snip

If myCell.Parent.Cells(myCell.Row, "B").Text < "GSD" And _
InStr(1, myCell.Parent.Cells(myCell.Row, "D").Text, "AAA", _
vbTextCompare) = 0 Then

End If

ought to work here.