Excel VBA UDF

[Young_Money]

I am trying to make a UDF that prints the text next to the column that is entered.
Here is an example of what my excel sheet looks like
A / B / C
Customer Initials Location
blahblah JJ Virginia
blah GL Texas
hello LO California
ohai FE Georgia
lalala SL New York
adsf JL Washington

I want to be able to type in the initials and get the location returned.
For example, if i type in =getlocation(SL) into any cell, it would return New York
You cannot really see what the sheet looks like here, but it is supposed to be three different columns.
Can anybody help me out with how to do this please?

 

[nightcrawler23]

why dont u use vlookup???

 

[Young_Money]

Never thought of that since I'm new to VBA. thanks for the tip! I'll check it out

 

[Young_Money]

I see how to use vlookup. However, the assignment is that I need to make a custom function, and not use any existing functions.

 

[RSXchin]

assignment eh....? homework? why you ask for help?

I'll give you hints, but not do your work for you.

1. find your string in the correct column, and then
2. return an OFFSET for the next column

 

[Young_Money]

Thanks rsxchin! I didn't want anybody to do it for me, I just wanted some tips so I would be able to do it because I actually want to learn it.

 

[RSXchin]

if you get one to work, post it and we'll pick it apart. or help you make it better

 

[Young_Money]

Here is what I have. I just started VBA yesterday, so I am REALLY new to this. I don't really know what is wrong with it...


Function CustomLookup(initials As String) As String
Dim r As String
For Each r In Range("B:B")
If r.String = r Then
CustomLookup = r.Offset(0, 1).Value
Exit Function
End If
Else
CustomLookup = "Not Found"
End If

End Function

My idea is that when i enter an initial such as FE, it will look in the column B for FE. If it is there, then it will look at the column to the right of it, and print it out Georgia. But I do not know how to fix the syntax error here..
Any more hints please?

 

[Young_Money]

hmm actually
would I be able to use vlookup to see if i could find a match in column b
and it matches, then return the offset 1 to the right?

 

[RSXchin]

for 1... Your missing your "Next" line. you need it for the "For" part to loop.