Excel vba import data from another workbook and compare

0
votes

I have one excel workbook "source" (with data parameters) like :

NAME PARAMS1 PARAMS2 ...
name1 param1_1 param1_2 ...
name2 param2_1 param2_2 ...
name3 param3_1 param3_2 ...

In another workbook "report" I have column for example "B" where I have my "names" as well like: .A.....B.....C
....name1...
....name1...
....name2...
....name3...
....name2...
...
Now I want to:
1. Import data from "source" to "report"
2. Check in column if I have the same name
3. If I find the same name I put value(param) of corresponding name in column for example"C"

I have one big Please. I need working example not advice like I should learn about Vlookup or collections or arrays.
regards
Jack

1
excelvba
Have you tried using a VLOOKUP on name? – Alex P
No. I didn't. But I have code using application.match refer to please:stackoverflow.com/questions/41242554/vba-make-code-simplier – awariat

1 Answers

1
votes

OK I managed to do it on my own.

Sub FBA_test_1()

Dim products As Variant
Dim productsWithParams As Variant
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

'products = Array("one", "two", "three")
products =Workbooks("sourceFile.xlsx").Worksheets("Sheet1").Range("A2:A4").Value

'productsWithParams = Array(Array("one", 10, 11), Array("two", 20, 21), Array("three", 30, 31))
productsWithParams = Workbooks("sourceFile.xlsx").Worksheets("Sheet1").Range("A2:C4").Value

For x = LastRow To 1 Step -1

    ' if value not found inside the array using the "MATCH" function
    pos = Application.Match(Range("$A$" & x).Value, products, 0)
    If IsError(pos) Then
        Range("$B$" & x).Value = "ERROR - "
    Else ' successful "MATCH" inside the array
        Range("$B$" & x).Value = pos
        Range("$C$" & x).Value = products(pos, 1) 'name
        Range("$D$" & x).Value = productsWithParams(pos, 2) 'param1
        Range("$E$" & x).Value = productsWithParams(pos, 3) 'param2

   End If
Next
End Sub

I use two arrays one normal and one 2d because 2d doesn't work with Application.Match
Regards