View Full Version : Getting/Searching Cell value from another Excel Worksheet
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
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
12-14-2010, 05:14 AM
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
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
12-14-2010, 01:41 PM
'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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.