VBA get value from differnt sheets

[Rushdy Najath]

Hi there I am not a VBA Programmer so its all new to me
What I am trying to do is alter an existing VBA code basically
I got 2 sheets
In sheet one I get the site id and now I wanted to create a function which take site id and a string Title as parameters and
Return a cell value from sheet two how can I do this

Eg.
In sheet1 I should be able to call

Name = GetName(SiteId,”SalesManager”)

Public Function GetName ( int SiteID, string Title ) as string

GetName = from st in Sheet2
Where st.siteid = SiteId and st.title = Title
Select st.fullname

End Function
Appreciate all your help
Thanks

[Domski]

If you could provide a sample workbook showing what you want to do it would help a great deal.

Dom

[Rushdy Najath]

sure
in sheet1 i got a cell (D) and SubjectID
and in sheet2 i got cell (A) named SiteId and cell(c)named Title and Cell(E) fullName
so i got a button in sheet1
the code behind the button
i got
Dim SiteID As String

SiteID =Mid(Sheet1.Cells(RowNumber, Columns(i)).Text, 1, 4)
so now what i wanted is i want a function which take
site id and a string
and retrun the FullName from sheet2 where it match the row of siteid and the Title
so i can call
like Name = GetName(SiteId,”SalesManager”)

[Domski]

No attachment.

Dom

[Rushdy Najath]

SORRY YES I HAVE CREATED A SAMPLE SHEET
IN SHEET ONE CELL A got siteID
so what i wanted is a fuction
where i can pass the site id and a string
and it should fatch the fullname from sheet to
eg
Name =GetName(123,"Sales Manager")
so Name ="Tony Cookes"

[Domski]

Why do you need to create a vba function for this when Vlookup would do the job?

Dom

[Rushdy Najath]

i am editing a part to the existing vba code of a button click it create a letter using the information from this two sheets.
all the vba codes are in sheet1 so now i wanted to get the information from sheet2

[Domski]

Maybe:

Public Function GetName(lngSiteID As Long, strTitle As String) As String

    Dim rngFindTitleRange As Range
    Dim rngFindSiteIDRange As Range

    Set rngFindTitleRange = Sheets("Sheet2").Range("1:1").Find(strTitle, LookIn:=xlValues, lookat:=xlWhole)
    If Not rngFindTitleRange Is Nothing Then
        Set rngFindSiteIDRange = Sheets("Sheet2").Range("A:A").Find(lngSiteID, LookIn:=xlValues, lookat:=xlWhole)
        If Not rngFindSiteIDRange Is Nothing Then
            GetName = rngFindSiteIDRange.Offset(0, rngFindTitleRange.Column - 1)
            Exit Function
        End If
    End If
    GetName = "#N/A"

End Function

Dom

[Rushdy Najath]

THANKS a lot mate i will post you back if worked
thanks again

[Rushdy Najath]

its throughing an error please help i have attached some of the screen shoots

[Domski]

Change

Sheets("Sheet2")

to

Sheets("Lookup")

Dom

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[Rushdy Najath]

ya its working thanks a lot to use this function for get more information how can i change rngFindSiteIDRange.Offset(0, rngFindTitleRange.Column - 1)
to sent a cell name so in that case
i am passing 3 pice of information siteid,title and the name of the cell i wanted to return
eg if i a pass

 GetName(123,"Sales Manager","Name")

this should retrun the value of cell named("Name")

 GetName(123,"Sales Manager","Main_Fax#" )

this should return the Main_Fax
thanks a lot for your help