Finding a value from another workbook

[jet]

I just started VBA on excel97 and have this question:

I place a value on Workbook A (e.g. A1 = cars) then I want to search the value from Workbook B(Column A), which is my reference. After the value is found (e.g. A10), I want to retrieve the value on the same workbook on column B (e.g. B10), the price for the car, then place the value on Workbook A (e.g. B1)...

Is that possible?

thanks,
jet

[Mike Fogleman]

Yes. The VLookup function will work fine across different workbooks. Put the
formula in B1 to find A1 in lookup table of workbook B.
The formula will look something like this:
=VLOOKUP(A1,[BookB.xls]Sheet1!$A$1:$B$100,2,FALSE)

No VBA needed.
Mike F
"jet" <[email protected]> wrote in message
news:[email protected]...
>
> I just started VBA on excel97 and have this question:
>
> I place a value on Workbook A (e.g. A1 = cars) then I want to search
> the value from Workbook B(Column A), which is my reference. After the
> value is found (e.g. A10), I want to retrieve the value on the same
> workbook on column B (e.g. B10), the price for the car, then place the
> value on Workbook A (e.g. B1)...
>
> Is that possible?
>
> thanks,
> jet
>
>
> --
> jet
> ------------------------------------------------------------------------
> jet's Profile:
> http://www.excelforum.com/member.php...o&userid=27461
> View this thread: http://www.excelforum.com/showthread...hreadid=469790
>

[Rowan]

Hi Jet

If you are wanting to implement in the most efficient manner then Mike's
vlookup suggestion is the way to go. If you were doing this as an
exercise in VBA then there are a number of ways to do it. One is:

Sub lkup()
Dim BookA As Workbook
Dim BookB As Workbook
Dim lVal As String
Dim fRng As Range

Set BookA = Workbooks("WorkbookA.xls")
Set BookB = Workbooks("WorkbookB.xls")
lVal = BookA.Sheets("Sheet1").Range("A1").Value
With BookB.Sheets("Sheet1").Columns(1)
Set fRng = .Find(lVal)
If Not fRng Is Nothing Then
BookA.Sheets("Sheet1").Range("B1").Value = _
fRng.Offset(0, 1).Value
End If
End With
End Sub

Regards
Rowan

jet wrote:
> I just started VBA on excel97 and have this question:
>
> I place a value on Workbook A (e.g. A1 = cars) then I want to search
> the value from Workbook B(Column A), which is my reference. After the
> value is found (e.g. A10), I want to retrieve the value on the same
> workbook on column B (e.g. B10), the price for the car, then place the
> value on Workbook A (e.g. B1)...
>
> Is that possible?
>
> thanks,
> jet
>
>

[jet]

Yes. The VLookup function will work fine across different workbooks. Put the
formula in B1 to find A1 in lookup table of workbook B.
The formula will look something like this:
=VLOOKUP(A1,[BookB.xls]Sheet1!$A$1:$B$100,2,FALSE)

No VBA needed.
Mike F
"jet" <[email protected]> wrote in message
news:[email protected]...
>
> I just started VBA on excel97 and have this question:
>
> I place a value on Workbook A (e.g. A1 = cars) then I want to search
> the value from Workbook B(Column A), which is my reference. After the
> value is found (e.g. A10), I want to retrieve the value on the same
> workbook on column B (e.g. B10), the price for the car, then place the
> value on Workbook A (e.g. B1)...
>
> Is that possible?
>
> thanks,
> jet
>
>
> --
> jet
> ------------------------------------------------------------------------
> jet's Profile:
> http://www.excelforum.com/member.php...o&userid=27461
> View this thread: http://www.excelforum.com/showthread...hreadid=469790
>

Thanks Mike, that helps a lot...

[jet]

Hi Rowan,

I tried your code below, however I got a
'run-time error 1004
Unable to get the find property of the range class
set fRng = .find(lVal)'

I experimented it using forms, I placed a textbox (txtsearch) so when i place a value on it, it will search for the value on workbookB (wbsource), also displaying it on the form(txtsource).

i wonder where i got it wrong...

my code looks like this:

'looks for the source file
Private Sub btnsource_Click()
Filesource = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If Filesource <> False Then
Application.ScreenUpdating = False
txtSource.Value = Filesource
Workbooks.Open (txtSource.Value)
txtSource.Value = ActiveWorkbook.Name
ThisWorkbook.Activate
Application.ScreenUpdating = True
End If
End Sub


'search value
Private Sub btnfind_Click()

Dim wbdestination As Workbook
Dim wbsource As Workbook
Dim lVal As String
Dim fRng As Range

Set wbdestination = Workbooks("destination.xls")
Set wbsource = Workbooks(txtSource.Value)
lVal = txtsearch.Value
With wbsource.Sheets(1).Columns(1)
Set fRng = .Find(lVal)
If Not fRng Is Nothing Then
wbdestination.Sheets(1).Range("D1").Value = _
fRng.Offset(0, 1).Value
End If
End With

End Sub



thanks,
jet

Hi Jet

If you are wanting to implement in the most efficient manner then Mike's
vlookup suggestion is the way to go. If you were doing this as an
exercise in VBA then there are a number of ways to do it. One is:

Sub lkup()
Dim BookA As Workbook
Dim BookB As Workbook
Dim lVal As String
Dim fRng As Range

Set BookA = Workbooks("WorkbookA.xls")
Set BookB = Workbooks("WorkbookB.xls")
lVal = BookA.Sheets("Sheet1").Range("A1").Value
With BookB.Sheets("Sheet1").Columns(1)
Set fRng = .Find(lVal)
If Not fRng Is Nothing Then
BookA.Sheets("Sheet1").Range("B1").Value = _
fRng.Offset(0, 1).Value
End If
End With
End Sub

Regards
Rowan

jet wrote:
> I just started VBA on excel97 and have this question:
>
> I place a value on Workbook A (e.g. A1 = cars) then I want to search
> the value from Workbook B(Column A), which is my reference. After the
> value is found (e.g. A10), I want to retrieve the value on the same
> workbook on column B (e.g. B10), the price for the car, then place the
> value on Workbook A (e.g. B1)...
>
> Is that possible?
>
> thanks,
> jet
>
>

[jet]

ooooops!!!
Sory Rowan, my mistake...


It was functional...

I'm so glad you tought me using VBA...

thanks,
jet

[skuzapo]

Hi Jet,

I'm trying to do the same thing as you (see my post on Vlookup in VBA) but I'm still confused after seeing your code....

My code is as follows:

Private Sub txtItem1_AfterUpdate()
Dim Item
Dim FileName As String
Dim MyRange As String
Dim BookStdCosts As Workbook
Dim BookCostings As Workbook
Dim lVal
Dim fRng As Range

Item = txtItem1.Value
Set BookStdCosts = Workbooks("Itemlist.xls")
Set BookCostings = Workbooks("Fantastic.xls")
lVal = Item
With BookStdCosts.Sheets(1).Columns(1)
Set fRng = .Find(lVal)
If Not fRng Is Nothing Then
BookCostings.Sheets("Costings").Range("a1").Value = _
fRng.Offset(0, 1).Value
End If
End With

If Item = "" Then
txtItem2.Visible = False
Else
txtItem2.Visible = True
End If

End Sub

I get an error message in the if statement but if I watch and step through the code, I can hover over "offset" and I can see that the correct data has been found. The error message I get is "subscript out of range".

Any suggestions anyone?
Thanks in advance.

[jet]

Hi there again,

I always get this error everytime i run my program:

"Run time error '1004':
Unable to get the FInd property of the Range class"

i'm using forms which includes the ff:
txtsupplierpath = textBox
txtquartersource= textBox
cmbsupplier = ComboBox
btnload = Button
btnloadsource = Button
opt1st = OptionButton

after i press on the optionbutton, i got the error,
here, i'm using 3 workbooks,
i'm not sure where my error is. Also, after the first value is found, i want to search the rest of the column for another value and place it in another cell, lets say under the first one...
need some assistance...


these are my codes:

Private Sub PopulateSupplier()
Dim i As Integer
i = 2
Application.ScreenUpdating = False
Workbooks.Open (txtSupplierPath.Value)
txtSupplierPath.Value = ActiveWorkbook.Name
ThisWorkbook.Activate
cmbSuppliers.Clear
Do
With Application.Workbooks(txtSupplierPath.Value).Sheets(2)
cmbSuppliers.AddItem (.Cells(i, 2))
If .Cells(i, 2).Value = "" Then
Exit Do
End If
i = i + 1
End With
Loop
Application.ScreenUpdating = True
On Error Resume Next
End Sub


Private Sub btnLoad_Click()
filetoopen = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If filetoopen <> False Then
txtSupplierPath.Value = filetoopen
PopulateSupplier
End If
End Sub


Private Sub btnloadsource_Click()
Filesource = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If Filesource <> False Then
Application.ScreenUpdating = False
txtQuarterSource.Value = Filesource
Workbooks.Open (txtQuarterSource.Value)
txtQuarterSource.Value = ActiveWorkbook.Name
ThisWorkbook.Activate
Application.ScreenUpdating = True
End If
End Sub


Private Sub cmbSuppliers_Change()
If cmbSuppliers.ListIndex > -1 Then
Sheet1.PopulateFields (cmbSuppliers.ListIndex + 2)
Sheet2.PopulateFields (cmbSuppliers.ListIndex + 2)
Sheet3.PopulateFields (cmbSuppliers.ListIndex + 2)
Sheet4.PopulateFields (cmbSuppliers.ListIndex + 2)
End If
On Error Resume Next
End Sub


Private Sub Opt1st_Click()
Dim wbdest As Workbook
Dim wbsource As Workbook
Dim lVal As String
Dim fRng As Range
Set wbdest = ActiveWorkbook
Set wbsource = Workbooks(txtQuarterSource.Value)
lVal = cmbSuppliers.Value
With wbsource.Sheets(2).Columns(2)

Set fRng = .Find(lVal) 'error in here

If Not fRng Is Nothing Then
wbdest.Sheets(2).Range("Q33").Value = _
fRng.Offset(0, 2).Value
End If
End With
End Sub


Private Sub UserForm_Activate()
If txtSupplierPath.Value <> "" Then
PopulateSupplier
End If
On Error Resume Next
End Sub


thanks,
jet