hi marcus see the attachment i tried to solve it with formulae let me know if you require the macro for it. On Tue, Feb 22, 2011 at 2:44 PM, Marcus <marcus.ost...@gmail.com> wrote:
> Hi
> Can anyone assist me, fixing this macro? I want it to lookup each name
> in Sheet1, find it in Sheet2 and return the ID number to Sheet1. The
> challenge is that the name lookup shouldn't be exact, i.e. "lisa
> flisa" equals "lisa".
>
> *Sheet1*
> Column C Column F
> Name ID
> kalle balle
> lisa flisa
> arne
> lisa flisa
>
> *Sheet2*
> Column B Column E
> Name ID
> arne 10
> lisa 20
> kalle 30
>
>
>
> Sub IterateNames()
> For i = 2 To 65535 Step 1
> c = Sheets("Sheet1").Range("C" & CStr(i)).Value
> If IsEmpty(c) Then Exit For
> Sheets("Sheet1").Range("F" & CStr(i)).Value = LookupName(c)
> Next i
> End Sub
> Private Function LookupName(ByVal name As String) As Variant
> LookupName = ""
> For i = 2 To 65535 Step 1
> b = Sheets("Sheet2").Range("B" & CStr(i)).Value
> If IsEmpty(b) Then Exit For
> If InStr(1, LCase(CStr(b)), LCase(CStr(name)),
> vbTextCompare) > 0 Then 'Here is the problem, I think
> LookupName = Sheets("Sheet2").Range("E" & CStr(i)).Value
> Exit For
> End If
> Next i
> End Function
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
*Regards*
* *
*Ashish Koul*
*akoul*.*blogspot*.com <http://akoul.blogspot.com/>
*akoul*.wordpress.com <http://akoul.wordpress.com/>
My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830>
P Before printing, think about the environment.
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
vlookup.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
