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

[Michael Levin]

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

 

[Mark Ivey]

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

 

[Joel]

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

 

[Dany Adams]

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

 

[Joel]

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]

I got it ! Thank you.

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