Getting/Searching Cell value from another Excel Worksheet

[Kayd]

Hi There, i am new to vba/macros. I have a worksheet (worksheet 1). In this worksheet i have a macro which needs a value from another worksheet (worksheet2).

In worksheet 2, i need to first find a value on the sheet ( i need to get row number). Once i get the row number i need to find a value for that row in another coloumn.

I tried basic VLookup (even with out using it VBA/Code) but could not manage to work.
Can some one please assist. Thanks

[GTO]

Greetings Kayd and welcome to vbaexpress:hi:

In worksheet 2, is the value we are looking for in a column left of the value we want to return?

Mark

[Benzadeus]

The code below will search for a value (strSearchValue) in a column (cData) of Sheet2 and put the result in Range A1 of Sheet1.

'You should put this in form of a function
Sub ReturnColumnFromMatch()

Dim strSearchValue As String
Dim c As Long
Dim cData As Long

strSearchValue = "MatchData"
'Number of columns to offset from matched value. Can be negative.
c = 2
cData = 3

Sheets("Sheet1").Range("A1") = _
Sheets("Sheet2").Columns(cData).Find(strSearchValue, , , xlWhole). _
Offset(, c)
End Sub

[Kayd]

Thanks for your reponses guys,

benzadeus,
The function you wrote, where do i specify the workbook name for example; like i mentioned i want to call workbook 2 from workbook 1 (vba Code).
In otherwords i am working on 2 excel spreadsheets

Mark,
for workbook 2, i want to search for a value in coloumn B, hoping to get the row number of a match, then get that value of that row # in coloumn D.
Hope that makes sense guys. Thnx

[Benzadeus]

'You should put this in form of a function
Sub ReturnColumnFromMatch()

Dim strSearchValue As String
Dim c As Long
Dim cData As Long

strSearchValue = "MatchData"
'Number of columns to offset from matched value. Can be negative.
c = 2
cData = 3

Workbooks("Book1").Sheets("Sheet1").Range("A1") = _
Workbooks("Book2").Sheets("Sheet2").Columns(cData).Find(strSearchValue, , , xlWhole). _
Offset(, c)

'If you want to return the row of the matched result,
'Workbooks("Book1").Sheets("Sheet1").Range("A1") = _
Workbooks("Book2").Sheets("Sheet2").Columns(cData).Find(strSearchValue, , , xlWhole).Row.Offset(, c)

End Sub