Looking up values in another file
 

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

Looking up values in another file
 

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)

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

Looking up values in another file
 

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!

Looking up values in another file
 

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!

Looking up values in another file
 

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)

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