VBA Excel User input lookup data different workbook

Question

I am on a very basic level of programming, however, as I am the "skilled" one in my company, I turn to you guys for support, I hope you can help me!

My task at hand is as follows.

I have a working workbook, where all the data entries are done and then I have a database file, also excel.

User file: Random name, as the sheet will change depending on the calculation done by the user. Sheet name "Sagsnr." (Caseno. in local language)

Sourcefile: "Matcost.xls", worksheet: "Matcost"

I need to be able to enter a value e.g. a material number or a range of numbers (So range is dynamic) into the active sheet, which will then start looking up specific field of data related to the ID (Mat. number) in the database file.

I will need to have calculation field in between the various data fetched from the database, so I cannot just copy an entire row, but need to fetch for each ID number a set of values from cells in the database to the user input file.

I would like to only fetch the values and not paste a vlookup function into the sheet as this is both "dangerous from a user manipulation point of view as well as making the sheet slow in my experience.

I have now struggled with the above for quite some time and I have tried to lookup various questions and answers on this site as well, but to no apparent working solution. This might be due to my lack of understanding, but I hope you can then either point me to the right answer or answer it here.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim material As String
Dim fndEntry As Range
Dim wb1 As Workbook, wb2 As Workbook

Set wb1 = ActiveWorkbook

material = wb1.ActiveCell.Value

' Find the corresponding value in the Database file

Workbooks.Open Filename:="G:\Backoffice\Tilbudsteam\Kostdatabase\Matcost.xls", ReadOnly:=True

Set wb2 = ActiveWorkbook

'Change the below Range address..
Set fndEntry = wb2.Range("C:C").Find(What:=material)

'Change the below Range address..
If Not fndEntry Is Nothing Then
    wb2.Range("B" & fndEntry.Row).Copy Destination:=wb1.ActiveCell.Offset(0, 1)
End If


End Sub

Thank you very much in advance!

User input sheet

No attempt at all? What are you working with? Whats the Sheet names, size of data etc? What you said is completely doable, but this site is for fixing and adding to code, not writing it out completely for you. — Skaterhaz
Hi, as al my attempts have been to adapt bit and pieces of different codes, I thought it irrelevant and non-value adding, as they probably do their job fine for their minor purpose, but not for my purpose. But of course I can give you some sheet specifics, sorry for not including them in the beginning: User file: Random name, as the sheet will change depending on the calculation done by the user. Sheet name "Sagsnr." (Caseno. in local language) Sourcefile: "Matcost.xls", worksheet: "Matcost" — Smeltet
I can add some of the codes I have tried, but my conclusion so far is that I am on a very wrong track, therefore I left it out. — Smeltet
please show to us what you want by giving input data and expected result in atleast screenshot. it will help us to know what you exactly trying to do — Karthick Gunasekaran

Skaterhaz Skaterhaz · Accepted Answer · 2016-07-14T07:38:23

You will probably need your method to be on the 'WorkSheet' itself to be able to note when you have entered a 'material number'

Private Sub Worksheet_Change(ByVal Target As Range)

...

End Sub

Inside this you will probably need a find method bewteen the ActiveSheet and the Database file

Dim material As Variant
Dim fndEntry As Range
Dim wb1 As Workbook, wb2 As WorkBook

Set wb1 = ActiveWorkbook

material = wb1.ActiveSheet.ActiveCell.Value

' Find the corresponding value in the Database file

Workbooks.Open Filename:="C:\Somewhere\DataBase.xls

Set wb2 = ActiveWorkbook

Change the below Range address..

Set fndEntry = wb2.Sheets("xxx").Range("A:A").Find(What:=material)

Change the below Range address..

If Not fndEntry Is Nothing Then
    wb2.Sheets("xxx").Range("B" & fndEntry.Row).Copy Destination:= wb1.ActiveSheet.ActiveCell.OffSet(0,1)
End If

Pinpointing exactly what you want to do without any code is unfeasible, but the above will do the job, it's just you'll have to change the addresses for certain cells, data..or expand what you need to return to the initial worksheet.

EDIT

This should solve your dynamic problem, It checks how many entries you have pasted, typed in, in column C, then returns the data for each of these. You'll need to change the destination column towards the bottom

Private Sub Worksheet_Change(ByVal Target As Range)

Dim material As Variant
Dim fndEntry As Range
Dim wb1 As Workbook, wb2 As WorkBook
Dim lr As Integer

If Not Target.Column = 3 Then
    Exit Sub
End If

Set wb1 = ActiveWorkbook

lr = wb1.Sheets("Sagsnr.").Range("C1:C" & rows.Count).End(xlUp).Row

If lr < 22 Then
    Exit Sub
End If

Workbooks.Open Filename:="G:\Backoffice\Tilbudsteam\Kostdatabase\Matcost.xls", ReadOnly:=True

Set wb2 = ActiveWorkbook

For i = 22 To lr

    material = wb1.Sheets("Sagsnr.").Range("C" & i).Value

    Set fndEntry = wb2.Sheets("Matcost").Range("C:C").Find(What:=material)

    If Not fndEntry Is Nothing Then
        wb2.Sheets("Matcost").Range("E" & fndEntry.Row).Copy Destination:= wb1.Sheets("Sagsnr.").Range("destination column - change me" & i)
    End If

Next i

wb2.Close

End Sub

VBA Excel User input lookup data different workbook

1 Answers