Col B: name Col A: number

[gsx]

Dear all<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have a code to search range A1:B250, where a user is given the opportunity to search among 250 foundations. The name of the foundation is located in column B, its number in column A. They often contain similar words (letters) and search often return several suggestions. My variable NewRange present the hits, which is forwarded to a ListBox where the user can select the correct one.
My question is if someone can help me to adjust my macro to return not only the name but the corresponding number as well (same row, the cell to left).<o></o>

Sub Copy_To_Another_Range() 'http://www.rondebruin.nl/find.htm (adjusted)

Dim FirstAddress As String
Dim StrPrompt As String
Dim MyArr As Variant
Dim rng As Range
Dim Rcount As Long
Dim i As Long
Dim NewRange As Range

With Application
   .ScreenUpdating = False
   .EnableEvents = False
End With
 
MyArr = Array(InputBox(StrPrompt))
Set NewRange = Sheets("SUMMER konto").Range("M10")
 
With Sheets("LISTE LEGATER").Range("A1:B300")<o:p></o:p>
   Rcount = 0<o:p></o:p>
   For i = LBound(MyArr) To UBound(MyArr)<o:p></o:p>
       Set rng = .Find(what:=MyArr(i), _
                       After:=.Cells(.Cells.Count), _
                       LookIn:=xlFormulas, _
                       lookat:=xlPart, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, _
                       MatchCase:=False)
       If Not rng Is Nothing Then
           FirstAddress = rng.Address
 
           Do
               Rcount = Rcount + 1
 
               rng.Copy NewRange.Range("A" & Rcount)<o:p></o:p>
               Set rng = .FindNext(rng)
 
           Loop While Not rng Is Nothing And rng.Address <> FirstAddress
       End If
   Next i
End With<o:p></o:p>
With Application
   .ScreenUpdating = True
   .EnableEvents = True
End With
Call UserForm
End Sub

<o></o>
Best regards
Geir<o></o>

 

[p45cal]

I always wondered what does "p45cal" mean?

It means absolutely nothing, however it does happen to be (a version of) leet for my first name (and nothing to do with .45 calibre guns, as some have assumed)

 

[p45cal]

I'm now trying to work out why [snip] p45cal returns the number in first cell and name in cell after

Because that's what you asked for!:

challenge now is to place the name in the cell after the number

I assumed that after meant to the right

 

[p45cal]

but unfortunately from current active window (my active window in code is ("LISTE LEGATER")

You can put a full stop (period) before
Cells
to make it
.Cells
to ensure the data comes from the sheet you're searching.

 

[gsx]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
thanks to you, my code is now complete.
it's a very good code for searching. Beside being fast it will give a helpful result even when the user don't know the exact spelling.
I have built a listbox which populate the result, the user then can select from (to produse a trial balance). <o></o>
With gratitude
Geir<o></o>

 Sub Copy_To_Another_Range() 'http://www.rondebruin.nl/find.htm<o:p></o:p>
    Dim FirstAddress As String<o:p></o:p>
    Dim StrPrompt As String<o:p></o:p>
    Dim MyArr As Variant<o:p></o:p>
    Dim rng As Range<o:p></o:p>
    Dim Rcount As Long<o:p></o:p>
    Dim i As Long<o:p></o:p>
    Dim NewRange As Range<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
    With Application<o:p></o:p>
        .ScreenUpdating = False<o:p></o:p>
        .EnableEvents = False<o:p></o:p>
    End With<o:p></o:p>
    <o:p></o:p>
    MyArr = Array(InputBox(StrPrompt))<o:p></o:p>
    Set NewRange = Sheets("SUMMER konto").Range("M10")<o:p></o:p>
    <o:p></o:p>
    With Sheets("LISTE LEGATER").Range("A1:B300")<o:p></o:p>
<o:p> </o:p>
        Rcount = 0<o:p></o:p>
<o:p> </o:p>
        For i = LBound(MyArr) To UBound(MyArr)<o:p></o:p>
<o:p> </o:p>
            Set rng = .Find(what:=MyArr(i), _<o:p></o:p>
                            After:=.Cells(.Cells.Count), _<o:p></o:p>
                            LookIn:=xlFormulas, _<o:p></o:p>
                            lookat:=xlPart, _<o:p></o:p>
                            SearchOrder:=xlByRows, _<o:p></o:p>
                            SearchDirection:=xlNext, _<o:p></o:p>
                            MatchCase:=False)<o:p></o:p>
            If Not rng Is Nothing Then<o:p></o:p>
                FirstAddress = rng.Address<o:p></o:p>
            <o:p></o:p>
                Do<o:p></o:p>
                    Rcount = Rcount + 1<o:p></o:p>
                    <o:p></o:p>
                    'gives the number<o:p></o:p>
                    rng.Offset(, -1).Copy NewRange.Range("A" & Rcount).Offset(, -1)<o:p></o:p>
                    'the name<o:p></o:p>
                    rng.Copy NewRange.Range("A" & Rcount)<o:p></o:p>
                <o:p></o:p>
                    Set rng = .FindNext(rng)<o:p></o:p>
                    <o:p></o:p>
                Loop While Not rng Is Nothing And rng.Address <> FirstAddress<o:p></o:p>
            End If<o:p></o:p>
        Next i<o:p></o:p>
    End With<o:p></o:p>
<o:p> </o:p>
    With Application<o:p></o:p>
        .ScreenUpdating = True<o:p></o:p>
        .EnableEvents = True<o:p></o:p>
    End With<o:p></o:p>
End Sub

<o></o>

 

[p45cal]

…which will crash when someone enters a number in the input box.

 

[gsx]

I know
"complete" was a wrong word to use. I will certainly, as best as I can, construct the search to prevent a crash
best regards