Looking up values in another file

T

Tim Patton

Hi All

I am new to VBE and would appreciate help with the following if you can:

I need to link two files. File 1 has a link number and two values, lets
called then A and B.

File 2 has a list of values and if they match the link number in File 1 I
want values A and B inserted in specific columns in File 2.

Sounds easy, but I am having real trouble getting anything written.

I would like to get the function to run when a new value is entered in the
Value column in File 2, but I am lost.

Hope somebody has had the same problem and might have a solution.

Tim
 
B

Bob Phillips

You would use change event code to trap the change.

You also need to have the File2 open, and need to save it to a specific
cell. You don't say what and where you lookin g at, or where A & B are.

This is an example of trapping the change

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
'do something
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Give more details of th erest to work out how to apply.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Tim Patton

File 1 ( The lookup file ) has three columns:

Column A: "Profile Number" with entries 1 to 1500
Column B: "Passes" with values 1 or 2
Column C: "Speed" with values 55, 60 or 40

So in File 2 ( the entry file ) A "profile number" is entered in column
A, I then want the prog to open File 1, match the profile numbers and
then copy the corresponding values in columns B and C to Columns E and F
in File 2.

Thanks for helping me out Bob...

Hope this provides a bit more detail.

Cheers mate

Tim



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
T

Tim Patton

File 1 ( The lookup file ) has three columns:

Column A: "Profile Number" with entries 1 to 1500
Column B: "Passes" with values 1 or 2
Column C: "Speed" with values 55, 60 or 40

So in File 2 ( the entry file ) A "profile number" is entered in column
A, I then want the prog to open File 1, match the profile numbers and
then copy the corresponding values in columns B and C to Columns E and F
in File 2.

Thanks for helping me out Bob...

Hope this provides a bit more detail.

Cheers mate

Tim



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
B

Bob Phillips

Tim,

Try this. I haven't tested it but hopefully it works. It assumes that the
value to be looked up is also in column A in file 1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oWb As Workbook
Dim oCell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
Set oWb = Workbooks.Open(Filename:="c:\test\file1.xls")
With oWb
On Error Resume Next
Set oCell = .Worksheets("Sheet1").Cells.Find(Target.Value)
If Not oCell Is Nothing Then
oCell.Offset(0.4).Value = Target.Offset(0, 1).Value
oCell.Offset(0.5).Value = Target.Offset(0, 2).Value
Else
MsgBox "Not found"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top