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>

 

[taurean]

Try changing following:

[FONT=Times New Roman]rng.Copy NewRange.Range("A" & Rcount)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT]

to something like:

[FONT=Times New Roman]rng.Resize(, 2).Copy NewRange.Range("A" & Rcount)<o:p></o:p>[/FONT]

 

[gsx]

Dear Shrivallabha,
thank you for your quick response.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I tried your suggestion, unfortunately without luck. The code only returns the name;
my goal is to place the number of the foundation in M10 and its name in N10<o></o>
Kind regards <o></o>

 

[taurean]

Sorry. Didn't read it carefully. Try:

[FONT=Times New Roman]rng.Offset(, -1).Copy NewRange.Range("A" & Rcount).Offset(, 1)[/FONT]

 

[gsx]

Dear Shrivallabha,
I have been speculating about this for the last two days, determined to use the offset function without finding the correct syntax.
Now, thanks to your replay something is happening! I feel quite close to a solution; you have with your suggestion placed the number in N10. The challenge now is to place the name in the cell after the number (your adjustment only rturned the number).
Best regards
Geir

​

 

[taurean]

Oops! Your revised Do Loop should look like:

[FONT=Times New Roman]Do[/FONT]
[FONT=Times New Roman][COLOR=black]               Rcount = Rcount + 1[/COLOR][/FONT]
 
[FONT=Times New Roman][COLOR=black]               rng.Copy NewRange.Range("A" & Rcount)[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=black]               [/COLOR][COLOR=red]rng.Offset(, -1).Copy NewRange.Range("A" & Rcount).Offset(, 1)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/COLOR][/FONT]
[COLOR=black][FONT=Times New Roman]               Set rng = .FindNext(rng)[/FONT][/COLOR]
 
[FONT=Times New Roman][COLOR=black]           Loop While Not rng Is Nothing And rng.Address <> FirstAddress[/COLOR][/FONT]

 

[p45cal]

Cells(rng.Row, 1).Resize(, 2).Copy NewRange.Range("A" & Rcount)

edit post posting: taurean, while I realise that generally, people would be expected to search by name, gsx's code does allow to search by number too, so it could be that rng is set to a cell in column A, when rng.offset(,-1) might not produce the right cell.

 

[p45cal]

gsx, if you only want to be able to search by name your line should be

With Sheets("LISTE LEGATER").Range("[B][COLOR=Red]B[/COLOR][/B]1:B300")

rather than:

With Sheets("LISTE LEGATER").Range("A1:B300")

 

[taurean]

edit post posting: taurean, while I realise that generally, people would be expected to search by name, gsx's code does allow to search by number too, so it could be that rng is set to a cell in column A, when rng.offset(,-1) might not produce the right cell.

Thank you p45cal. Thats correct observation and as I see, you have taken care of it in your next post!

You have helped me in the past as well (on VBAX)...I always wondered what does "p45cal" mean?

 

[gsx]

dear p45cal and taurean,

I'm so happy! Instead of speculating for another week I rather ask this board! A board where I come across such nice people.......
I went to the shelf with my cd collection and picked out the most wonderful Beethoven 4. piano concerto.
I'm now trying to work out why taurean return the name before the number, and p45cal returns the number in first cell and name in cell after, but unfortunately from current active window (my active window in code is ("LISTE LEGATER").

Thank you very much!
Geir