Excel 2007, How to find all cells reference number of a text Hi^_^ Need to find

Question

Excel 2007, How to find all cells reference number of a text

Hi^_^

Need to find the Rows number for a text.

In Cell P3 we type the phone number we need to search

When I type the phone number in that cell I want to see in P4 all the rows number we can get hat phone number.

Example 5141112222 in P3

Data is in C12 to C10,000 and this phone number exist already in column C20, C45,C8500

This mean in P4 I should see 20,45,8500.

If no function already exist I will probably need to create a VBA function

What function or codes I need to get this?

PS> I'm familiar with vba or creating a function.

Thanks a lot

Explanation / Answer

T%^ry the function below. After entering into the Visual Basic Editor, enter a number to search in P3, then enter the formula "=FindPhoneNumber(P3,C12:C10000)" in P4.

Hope this helps,

Option Explicit

Function FindPhoneNumber(whichNumber As Variant, searchRange As Range) as String

    Dim i As long

    Dim searchArray() As Variant

    Dim strRows As String

'

    If (searchRange is Nothing) Then

        FindPhoneNumber = "EMPTY SEARCH RANGE!"

        Exit Function

    End If

'

    searchArray = searchRange.Resize(searchRange.Rows.Count, 1)

'

    strRows = ""

    For i = 1 To searchRange.Rows.Count

        If (StrComp(searchArray(i, 1), whichNumber) = 0) Then strRows = strRows & searchRange.Row + i - 1 & ", "

    Next i

    If (Len(strRows) > 0) Then strRows = Left(strRows, Len(strRows) - 2)

    FindPhoneNumber = strRows

End Function

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.