how to use VB code to define vlookup in two workbook
 

Dear friends,

I try to combine data between two worksheet using VB. One workbook
calls SAN kept in c:\test\import, another workbook calls WYE kept in
c:\test\backup

SAN Workbook has one sheet (sheet1)
COLUMN A B
C034001
C042001
C054021

WYE Workbook has one sheet(code)
column A B
C034001 WYE1
C042001 WYE2
C054021 WYE3


Now want to use vb code to copy WYE column B value to SAN column B
based on the column A. But always failed. Could anybody help me. Thanks
so much

My current some code as below
set backxl=new excel.application
set destxl=new excel.applicaiton

strDestF=C:\test\import\san.xls
strBackF=c:\test\backup\wye.xls
destXL.Workbooks.Open strDestF
backXL.Workbooks.Open strBackF
i=0


I try to use vlookup to get value from wye.xls to san.xls, but failed
destXL.Cell(i + 2, 2) = "vlookup('" & destXL.Cells(i + 2, 1) & "'," &
"[strBackF]Code!A$A$2" & ":" & "$B$4" & ",2)"

Regards


--
yihong
------------------------------------------------------------------------
yihong's Profile: http://www.excelforum.com/member.php...o&userid=25018
View this thread: http://www.excelforum.com/showthread...hreadid=385473

how to use VB code to define vlookup in two workbook
 

how about using find command to get the row and just refer to the cell
column and row?

the below is an example

tender = Sheets("km").Cells(j, colkm).Text
Set rng = Sheets("nominated").Range("nom").Find(tender)
If rng Is Nothing Then GoTo badkmtender
nomrow = rng.Row


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=385473

how to use VB code to define vlookup in two workbook
 

Hi, Duane

Thanks for your reply. Because wye.xls has many codes. A is our company
code, B is our customer code. So use Vlookup I think is the fastest way
to get the customer code from WYE.xls and paste to SAN.XLS.

Regards


--
yihong
------------------------------------------------------------------------
yihong's Profile: http://www.excelforum.com/member.php...o&userid=25018
View this thread: http://www.excelforum.com/showthread...hreadid=385473

how to use VB code to define vlookup in two workbook
 

Dear friends,

I used VB (excel.application) to open two excel file (One called
AAA.XLS is standard code, another called BBB.XLS will get code from
AAA.XLS), after finishing, I use .Quit to exit both of excel file. Then
again to open another excel file CCC.XLS to get AAA.XLS code, but
failed. I suspected excel or workbook or temp file didn't acutually
close. Below is part of my codeing, when second time run to
destXL.Cells(iRowDest, 31) = Application.VLookup(findString, rang1, 2,
False), will failed. Here destxl is the example BBB.XLS or CCC.XLS,
backxl likes AAA.XLS.
I tried many times, but all failed. Anyone can help. Thanks so much!

Set backXL = New excel.Application 'SANKYU AND WYETH CODE
Set destXL = New excel.Application 'NEED ADD WYETH CODE

destXL.Workbooks.Open strDestF
destXL.Cells(1, 31) = "WYETH"

backXL.Workbooks.Open strBackF
'Do While backXL.Cells(backCount, 1) < ""
' backCount = backCount + 1 'get how many wyeth code in template
'Loop

'
Do While destXL.Cells(iRowDest, 30) < ""
Dim findString As String
Dim rang1 As Range

Set rang1 = backXL.Range("A2:B10000")
findString = destXL.Cells(iRowDest, 30)
destXL.Cells(iRowDest, 31) = Application.VLookup(findString, rang1,
2, False)

iRowDest = iRowDest + 1
sourceCount = iRowDest

Loop
destXL.Cells(sourceCount, 25) = "=SUM(Y2:Y" & sourceCount - 1 & ")"
lbxFile.Refresh
Kill (strSourceF)
MsgBox "Finish to add Wyeth Code!", vbOKOnly, "Finish"
'clean up and exit
Set oWS = Nothing
If Not oWB Is Nothing Then oWB.Close
Set oWB = Nothing
destXL.Quit
backXL.Quit
'Kill (strBackF)
btnExport.Enabled = False
Set destXL = Nothing
Set backXL = Nothing
Application.Quit
btnExport.Enabled = False
btnConfirm.Enabled = False
excel.Application.Quit
'Application.ActiveWindow.Close
'Unload Me
Exit Sub


--
yihong
------------------------------------------------------------------------
yihong's Profile: http://www.excelforum.com/member.php...o&userid=25018
View this thread: http://www.excelforum.com/showthread...hreadid=385473