I'm trying to write a function that can look at the values in eight columns
of a row, pick the two columns that have actual numbers (as opposed to NAs),
then compare those two numbers to another pair of numbers. Anyone know how
to write a function that can search that way ?

Thanks,

Mike

For each row... these two numbers could be added to an array and then used
as a comparison tool.

Where are the other two number going to be located? Will they be located
there for each row that is evaluated?

Mark

"Michael Levin" wrote in message
...
I'm trying to write a function that can look at the values in eight
columns
of a row, pick the two columns that have actual numbers (as opposed to
NAs),
then compare those two numbers to another pair of numbers. Anyone know
how
to write a function that can search that way ?

Thanks,

Mike

Sub get_data()

Dim MyNumbers(2)

MyRow = 5
MyStartCol = Range("B2").Column
MyEndCol = Range("I2").Column
Count = 0
For i = MyStartCol To MyEndCol
If Not WorksheetFunction.IsNA(Cells(MyRow, i)) Then
MyNumbers(Count) = Cells(MyRow, i)
If Count = 1 Then Exit For
Count = Count + 1

End If
Next i

End Sub


"Michael Levin" wrote:

I'm trying to write a function that can look at the values in eight columns
of a row, pick the two columns that have actual numbers (as opposed to NAs),
then compare those two numbers to another pair of numbers. Anyone know how
to write a function that can search that way ?

Thanks,

Mike

The two numbers to which the ³found² pair will be compared will be in the
same row, always in the same two columns. eg:

2 5 1 6 #NA #NA #NA #NA #NA #NA
6 2 #NA #NA #NA #NA 2 2 #NA #NA
3 2 #NA #NA 3 7 #NA #NA #NA #NA
1 4 #NA #NA #NA #NA #NA #NA 6 8


On 4/3/08 7:19 AM, in article
, "Mark Ivey"
wrote:

For each row... these two numbers could be added to an array and then used
as a comparison tool.

Where are the other two number going to be located? Will they be located
there for each row that is evaluated?

Mark

"Michael Levin" wrote in message
...
I'm trying to write a function that can look at the values in eight
columns
of a row, pick the two columns that have actual numbers (as opposed to
NAs),
then compare those two numbers to another pair of numbers. Anyone know
how
to write a function that can search that way ?

Thanks,

Mike

I got it ! Thank you.

On 4/3/08 11:26 AM, in article
, "Joel"
wrote:

I modified my code from a subroutine to a function

call with
=compare_data(C4:J4,A4,B4)

Function compare_data(Data_Range As Range, First_Val, Second_Val)

Dim MyNumbers(2)

Count = 0
For Each cell In Data_Range
If Not WorksheetFunction.IsNA(cell) Then
MyNumbers(Count) = cell
If Count = 1 Then Exit For
Count = Count + 1

End If
Next cell

If MyNumbers(0) = First_Val And _
MyNumbers(1) = Second_Val Then

compare_data = True
Else
compare_data = False
End If
End Function


"Dany Adams" wrote:

The two numbers to which the ©øfound©÷ pair will be compared will be in the
same row, always in the same two columns. eg:

2 5 1 6 #NA #NA #NA #NA #NA #NA
6 2 #NA #NA #NA #NA 2 2 #NA #NA
3 2 #NA #NA 3 7 #NA #NA #NA #NA
1 4 #NA #NA #NA #NA #NA #NA 6 8


On 4/3/08 7:19 AM, in article
, "Mark Ivey"
wrote:

For each row... these two numbers could be added to an array and then used
as a comparison tool.

Where are the other two number going to be located? Will they be located
there for each row that is evaluated?

Mark

"Michael Levin" wrote in message
...
I'm trying to write a function that can look at the values in eight
columns
of a row, pick the two columns that have actual numbers (as opposed to
NAs),
then compare those two numbers to another pair of numbers. Anyone know
how
to write a function that can search that way ?

Thanks,

Mike