[Please help :( ] Functions to return values

alexchan

New Member
Joined
Feb 19, 2016
Messages
2
ABCD
1 HelloHiWorld
2=results(B2:D2)NANA11
3=results(B3:D3)NANANA
4=results(B4:D4)22127
5=results(B5:D5)NA80


<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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Welcome to the Board!

Try this:
Code:
Function Results(arr As Range) As String

    Dim rng As Range

    Results = "NA"

    For Each rng In arr
        If IsNumeric(rng.Value) Then
            Results = Cells(1, rng.Column)
            Exit Function
        End If
    Next rng
    
End Function
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top