A | B | C | D | |
1 | Hello | Hi | World | |
2 | =results(B2:D2) | NA | NA | 11 |
3 | =results(B3:D3) | NA | NA | NA |
4 | =results(B4:D4) | 2 | 21 | 27 |
5 | =results(B5:D5) | NA | 8 | 0 |
<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>
Hi, I want to write a function (as shown in A2:A5), so that if it find the first digit in a user-defined range,it will return the value of the first row of that digit's column. If no digits, then display "NA".
For example,
A2 will return World as first digit is in col.D,and the value in D1 is World, given the range(B2:D2)
A3 will return NA as no digit was found in the given range(B3:D3)
A4 will return Hello as first digit is in col.B,and the value in B1 is Hello, given the range(B4:D4)
A5 will return Hi as first digit is in col.C,and the value in C1 is Hi, given the range(B5:D5)
I tried to write it but it does not work...Could anyone help me..??
Function Results(arr As range)
Dim rng As range
Dim Results1 As String
Dim Results2 As String
Dim i As Integer
Results1 = range(columns(,rng)&"1").Value
Results2 = "NA"
Do
Select Case IsNumeric(rng.Value)
Case Is = True
Results = Results1
Case Is = False
rng=rng.offset(0,1)
End Select
loop
End Function