Finding a value from another workbook
 

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 searc
the value from Workbook B(Column A), which is my reference. After th
value is found (e.g. A10), I want to retrieve the value on the sam
workbook on column B (e.g. B10), the price for the car, then place th
value on Workbook A (e.g. B1)...

Is that possible?

thanks,
je

--
je
-----------------------------------------------------------------------
jet's Profile: http://www.excelforum.com/member.php...fo&userid=2746
View this thread: http://www.excelforum.com/showthread.php?threadid=46979

Finding a value from another workbook
 

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" wrote in message
...

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

Finding a value from another workbook
 

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

Finding a value from another workbook
 

Mike Fogleman Wrote:
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" wrote in
message
...

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
------------------------------------------------------------------------
jet's Profile: http://www.excelforum.com/member.php...o&userid=27461
View this thread: http://www.excelforum.com/showthread...hreadid=469790

Finding a value from another workbook
 

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


Rowan Wrote:
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
------------------------------------------------------------------------
jet's Profile: http://www.excelforum.com/member.php...o&userid=27461
View this thread: http://www.excelforum.com/showthread...hreadid=469790

Finding a value from another workbook
 

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

It was functional...

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

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

Finding a value from another workbook
 

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 ste
through the code, I can hover over "offset" and I can see that th
correct data has been found. The error message I get is "subscript ou
of range".

Any suggestions anyone?
Thanks in advance

--
skuzap
-----------------------------------------------------------------------
skuzapo's Profile: http://www.excelforum.com/member.php...fo&userid=2743
View this thread: http://www.excelforum.com/showthread.php?threadid=46979

Finding a value from another workbook
 

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,
want to search the rest of the column for another value and place it i
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).Sheet s(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 HER

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,
je

--
je
-----------------------------------------------------------------------
jet's Profile: http://www.excelforum.com/member.php...fo&userid=2746
View this thread: http://www.excelforum.com/showthread.php?threadid=46979