Hi Dave,

Without otherwise looking at your code, change:

Match

to

Application.Match

Match is an Excel function not a VBA function.


---
Regards,
Norman

--

---
Regards,
Norman



"david shapiro" wrote in message
...

Norman and others,

Thanks for the new suggestion on changing the range in an advanced
filter. I`ll try that.

I`m wondering - it`s rather urgent, under deadline today very soon - I`m
coming up with an error in this code. Could you take a look at it and,
if possible, check the whole code for any bugs.

The first error that comes up is in the sub standardizeyears. It stops
at the word "match" in the following line, and says "compile error: sub
or function not defined".

Do Until Cells(intRowx + 1, intCol).value = "" ' Loop until end of
data
If Match(Cells(intRowx, intCol).value, years, 0) = "#N/A" Then
Call nonstandardyear(intRowx, intCol)

This is the whole code. Sorry about the rush. Thanks, would be much
appreciated. -- Dave

Sub preparationfinaldata()
Call standardizeyears
Call standardizesubgroup
End Sub
Sub standardizeyears()
Dim intRowx
Dim intCol
Dim years As Integer

intRowx = 1 ' Start in the first row
intCol = 2 ' The column in "final data" that contains the Years
to check

'Create a reference in the Standards Worksheet called "Years" for
the year columns
Do Until Cells(intRowx + 1, intCol).value = "" ' Loop until end of
data
If Match(Cells(intRowx, intCol).value, years, 0) = "#N/A" Then
Call nonstandardyear(intRowx, intCol)
End If
intRowx = intRowx + 1 ' Increment to next row
Loop
End Sub
Sub nonstandardyear(r As Integer, c As Integer)
Dim intLastCol
Dim intYear

intLastCol = 6 'Last nonempty column of excel sheet
intYear = Cells(r, c).value
Select Case Val(Cells(r, c).value)
Case 1990 To 1999
Cells(r, c).value = VLookup(mround(Cells(r, c).value, 5),
years, 1, False)
Case Else
Cells(r, c).value = VLookup(mround(Cells(r, c).value, 10),
years, 1, False)
End Select
Cells(r, intLastCol).value = "This data refers to" & intYear

End Sub
Sub standardizesubgroup()
Dim intRowx
Dim intCol

intRowx = 1 ' Start in the first row
intCol = 6 ' The column in "final data" that contains the Years
to check

'Create a reference in the Standards Worksheet called "subgroup" for
the subgroup columns
Do Until Cells(intRowx + 1, intCol).value = "" ' Loop until end of
data
If Match(Cells(intRowx, intCol).value, subgroup, 0) = "#N/A" Then
Call nonstandardsubgroup(intRowx, intCol)
End If
intRowx = intRowx + 1 ' Increment to next row
Loop
End Sub
Sub nonstandardsubgroup(r As Integer, c As Integer)
Dim strDigits As String
Dim intDigits As Integer
Dim intLastCol

intLastCol = 6

intDigits = Val(Left(Cells(rowx, col).value, 2)) + 5
strDigits = Str(intDigits)

If Cells(r, intLastCol) = "" Then
Cells(r, intLastCol) = VLookup(strDigits, subgroup, 1, True)
Else
Cells(r, intLastCol) = Cells(r, intLastCol) & VLookup(strDigits,
subgroup, 1, True)
End If


End Sub




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