How to look at the values in 8 columns, pick the two that havenumbers, then compare to another pair of numbers
 

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

How to look at the values in 8 columns, pick the two that have numbers, then compare to another pair of numbers
 

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

How to look at the values in 8 columns, pick the two that have num
 

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

How to look at the values in 8 columns, pick the two thathave numbers, then compare to another pair of numbers
 

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

How to look at the values in 8 columns, pick the two that have
 

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