to amend vba code index and match for a new file format RRS feed

  • Question

  • I have an imput sheet and database sheet in same workbook.

    the lookup value in imput sheet is in column C and the lookup array is column C of database .

    If lookup value in column C of imput sheet is found in column C of database sheet, then extract data of column A and

    column B of database and update column A and column B of imput sheet.

    the original file test.xlsm.

    So far the new template has changed ,the imput sheet has changed, column A and B has moved to column H and I ,

    and database the array lookup has moved now to column B and extract value are now at column D and E

    file amended column.xlsm

    I am not a vba coder, and looking help to amend the code below which is found test. xlsm, the (original code)

    to adjust code for file column amended .xlsm

    Thanks if someone can assist

    Sub updatedata() Dim x As Long Dim arr() As Variant Dim temp As Variant Dim ws1 As Worksheet Dim ws2 As Worksheet Dim dic As Object Const delim As String = "|" Set ws1 = Sheets("Imput Datasheet") Set ws2 = Sheets("Product DataBase ") Set dic = CreateObject("Scripting.Dictionary") Application.ScreenUpdating = False With ws2 For x = 1 To .Cells(.Rows.Count, 3).End(xlUp).Row dic(.Cells(x, 3).Value) = .Cells(x, 1).Value & delim & .Cells(x, 2).Value Next x End With With ws1 x = .Cells(.Rows.Count, 3).End(xlUp).Row arr = .Cells(2, 1).Resize(x - 1, 4).Value2 For x = LBound(arr, 1) To UBound(arr, 1) If dic.exists(.Cells(x + 1, 3).Text) Then temp = Split(dic(.Cells(x + 1, 3).Text), delim) arr(x, 1) = temp(0) arr(x, 2) = temp(1) Erase temp Else arr(x, 1) = Empty arr(x, 2) = Empty End If Next x .Cells(2, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value2 = arr End With Application.ScreenUpdating = True Set dic = Nothing Set ws1 = Nothing Set ws2 = Nothing Erase arr End Sub

    • Edited by Jean_7 Monday, March 19, 2018 5:26 PM
    Monday, March 19, 2018 5:25 PM

All replies

  • Hi Jean_7,

    If my understanding about your question is correct, code would be like this:
    I made a button [Update] on sheet [Input Datasheet].
    ' --- [Update] button
    Private Sub btn_Update_Click()
        ' ---[note] last character is space in your original sheet name [Product Database]
        ' ---------  I removed the last space ---
        Dim lastRowInput As Long
        Dim lastrowProduct As Long
        lastRowInput = Sheets("Input Datasheet").Cells(Rows.Count, 3).End(xlUp).Row
        lastrowProduct = Sheets("Product Database").Cells(Rows.Count, 3).End(xlUp).Row
        ' ---
        Dim targetRange As Range
        Set targetRange = Sheets("Product Database").Range("C2:C" & lastrowProduct)
        Dim findRange As Range
        Dim foundRow As Long
        Dim prodCode As String
        ' ---
        Dim myRowInput As Long
        For myRowInput = 2 To lastRowInput
            prodCode = Sheets("Input Datasheet").Cells(myRowInput, 3).Value
            Set findRange = targetRange.Find _
                (what:=prodCode, LookIn:=xlValues, lookat:=xlWhole)
            If (findRange Is Nothing) Then
                ' --- not found: do nothing
                ' --- Product Code found
                foundRow = findRange.Row
                Sheets("Input Datasheet").Range(Cells(myRowInput, 1), Cells(myRowInput, 4)).Copy _
                    Sheets("Product Database").Cells(foundRow, 1)
            End If
        ' ---
        MsgBox "[Update] completed !!"
        Sheets("Product Database").Select
    End Sub

    Ashidacchi >>

    • Edited by Ashidacchi Tuesday, March 20, 2018 3:09 AM
    Tuesday, March 20, 2018 2:48 AM
  • Hi Ashidacchi

    Thanks for assistance, but I am bit lost, see attached link ,the code works but did not find anything , the imput datasheet column H and I need to be updated as from database ,


    Imput sheet (from column F )

    Product Code 

    Database to lookup

    that is lookup in B product code for 
    1702049, if match extract, if match extract from column D ad E coding reference , coding description and insert it on imput sheet column H and I of the corresponding lookup value, if not found leave blank

    The code remains same as per index and match test.xlsm  but 

    for indexmatch test amended columns A, the code needs to be amended to the right column for both sheets

    Attached link file with your code inside , but  the original need to be adjusted as the columns have moved from both sheet compared the to original file (the amended file with your code where the original code needs to be amended as columns has moved )  (original index match file is ok)

    Hope it helps




    Tuesday, March 20, 2018 5:29 AM
  • Hi Jean_7,

    I've shared my sample file via OneDrive(IndexMatch, before I will examine you file.
    Please download and check my sample.

    Now, I'll start to examine... !! I cannot run M1 macro !!


    Ashidacchi >>

    • Edited by Ashidacchi Tuesday, March 20, 2018 6:05 AM
    Tuesday, March 20, 2018 6:05 AM
  • Hi Jean_7,

    I modified a file "IndexMatch test Amended Columns A.xlsm" and shared it as a zip file.
    IndexMatch test Amended Columns

    I would like to confirm:
    (1) There are many dupicated [Product Code] in sheet [Product Database].
      Is this (Duplicated Product Code) valid state?

    My code does not support duplicated key. i.e. modify a single row which is found first.
    If (1) is valid state with [Product Database], I can modify code in order to support multiple keys.


    Ashidacchi >>

    Tuesday, March 20, 2018 6:44 AM


    Kindly use the original file , module 1 ,  I have made  button,  column A and B of imput sheet is blank , when click button, it updates column A and B of imput sheet based on database .

    It gives an idea how it works.

    On previous amended column xlsm, the columns for imput and database lookup has changed , need the M1 macro on that file to be adjusted 

    Check your file, not exactly , it is the opposite



    • Edited by Jean_7 Tuesday, March 20, 2018 6:55 AM
    Tuesday, March 20, 2018 6:54 AM
  • Hi Jean,

    Firstly, I cannot open your link. It says I need to have privilege. 

    English is not my first language. Therefore, I'm not sure if  I can correctly understand what you mentioned.

    Is it "MUST" that I user/modify your macro M1? Doesn't my code satisfy your requirement?


    Ashidacchi >>

    I don't work for Microsoft. I'm a software developer self-employed.
    I hope you will remember this.
    • Edited by Ashidacchi Tuesday, March 20, 2018 7:04 AM
    Tuesday, March 20, 2018 7:03 AM

    Hi  Ashidacchi,

    See attached on microsoft drive ,I was not my intention that your code was not good .

    Perphas,  you have not seen the file as it M1 macro ,that my fault I apologize ,as I think it was not working on your side.

    I am not programmer much more a user ,english is not my native language which we both in common.

    I was just asking assistance to modify the code as column has move  in the attachement.

    If you look at new drive attachment, sure you will sure understand how the M1 code is working on the original file.



    Tuesday, March 20, 2018 7:43 AM
  • Hi Jean,

    Thank you for reply. But...

    I downloaded and opened your file "Original indexmatch .xlsm".
    And I found sheet tabs (usually it appears in the bottom of Excel window) unseen. So, I cannot switch between sheets and cannot distinguish which sheet I am seeing now.
    Scroll bar both vertical and horizontal does not appear.

    Which file should I download/use? Please provide file name.


    Ashidacchi >>

    Tuesday, March 20, 2018 8:03 AM
  • Hi Jean,

    Please forget my previous post.
    I downloaded two files as one zip file, and unzipped it, and then I could open two Excel files normally.

    And this will be my last post of today:
    Which file should I use?
     (a) indexmatch test amended columns A.xlsm
     (b) Original indexmatch .xlsm


    Ashidacchi >>

    Tuesday, March 20, 2018 8:19 AM
  • Hi  Ashidacchi,

    The file you opened is the original source file, on my side it opens correctly .

    Using excel 2016

    Hide or display scroll bars in a workbook - Excel - Office Support

    <cite class="iUh30" style="color:#006621;font-style:normal;font-size:14px;"></cite>

    <cite class="iUh30" style="color:#006621;font-style:normal;font-size:14px;"></cite>

    Where are my worksheet tabs? - Excel - Office Support - Office 365

    <cite class="iUh30" style="color:#006621;font-style:normal;font-size:14px;"></cite>

    So the screenshoot  is the imput datasheet, there a second sheet next product database in same workbook 

    Now test amended column A.xlsm is the file to amend to code to the correct column as below

    Ignore one screenshot above posted twice  database screen shoot 



    • Edited by Jean_7 Tuesday, March 20, 2018 8:29 AM
    Tuesday, March 20, 2018 8:21 AM
  • Jean_7,

    I will read your M1 macro tomorrow.

    Please specify file name that I should open/use.
    Sorry, I don't want to open/use two files.


    Ashidacchi >>

    Tuesday, March 20, 2018 8:28 AM
  • Ashidacchi 

    No problem both files have M1 macro



    the first file index match test amended columns A is the file that needs modification as the columns have move in this file

    Tuesday, March 20, 2018 8:34 AM
  • the first file index match test amended columns A is the file that needs modification as the columns have move in this file

    Hi Jean,

    (1) Sorry, I can hardly understand the meaning of "the first file index match test amended columns A is the file that needs modification as the columns have move in this file". Could you explain it?

    (2) In the sheet "Product Database", there many duplicated [Product Code]. Is this right/normal state?

    (3) You says "I am not programmer much more a user", but I'm afraid, in your macro, you are using somewhat difficult or advanced features for beginners. I guess that your code is copied from somewhere or is made by a predecessor in your office.
    Is this correct? If you copy code from somewhere, please provide its source/URL?


    Ashidacchi >>

    Wednesday, March 21, 2018 1:14 AM
  • Hi Jean,

    You cannot use "Dictionary", because sheet "Product Database" has duplicated keys.
    "Dictionary" cannot have more than one same key in it (keys must be unique).

    In short, firstly you need to remove duplicated keys (in your case, combination of "Coding Reference" and "Coding Description").

    # Please provide your time-zone (UTC). I live in Japan and its UTC is +9.0. I would like to think about your macro while you are awake.


    Ashidacchi >>

    • Edited by Ashidacchi Wednesday, March 21, 2018 4:50 AM
    Wednesday, March 21, 2018 2:05 AM
  • Hi Jean,

    Sorry I've given up modifying your macro with Dictionary and Array.

    I give you my final gift that has made "Product Database" unique. 
    Please download and use my code: 
    Ash_Original IndexMatch .zip!AhzOJeY5F3-fj95zmXXZ3Gl2ZCuiqw


    When you write code/macro, remember to make data clean. If data is dirty (in your case, duplicated key exist), your code/macro won't work as you expect.

    Ashidacchi >>

    Wednesday, March 21, 2018 10:53 PM