views:

42

answers:

2

Hi,

I have something of the following :

Sheet 1

(Name Range : Code)
Column A -------- Column B

School ------------ 1
College ----------- 2
University--------- 3

Sheet 2
Cell A1 = (Search for "College" in Name Range Code and get the Value of "2" as code. ) So value of 2 should be present in Cell A1.

Please provide excel VBA

A: 

Use the VLOOKUP function.

Which is Application.WorksheetFunction.VLookUp in VBA.

GSerg
ok. But, I will be accessing it in 2 different worksheets. Its like searching for string in one worksheet and returning in a separate worksheet.
Rocky
So, in worksheet 1, I will write a VBA to go and search string in worksheet 2, get the value from adjacent column and return to worksheet 1.
Rocky
So, at the end of the day, what do you need? A formula on a sheet or a piece of code? Your use of 'VBA' is quite confusing.
GSerg
Its kind of a macro. Its VBA code. Not a formula on sheet. You hit a Button, Macro runs the VBA and searches for the text.
Rocky
Then use `Worksheets("Sheet 2").Range("Code")` as the source table for `VLookUp` to search in.
GSerg
The following code does not work :Dim Res As StringOn Error Resume Next Err.Clear Res = Application.WorksheetFunction.VLookup("College", Worksheets("Sheet 2").Range("Code"), 2, False)
Rocky
Works for me. Make sure `Sheet 2` exists, and `Code` is actually defined on `Sheet 2` and has at least two columns.
GSerg
A: 

This works for me:

Sub LookUpValue()
    Dim searchValue As String
    Dim lookupTable As Range

    Set lookupTable = Worksheets("Sheet1").Range("Code")
    searchValue = "College"

    Worksheets("Sheet2").Range("A1") = WorksheetFunction.VLookup(searchValue, lookupTable, 2, False)

 End Sub

Hope this helps...

Remnant
Thanks. The code works.
Rocky