Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

Index Match within VBA



I have an error message and don't know how to continue. 

By using File A data, I need to look up values in File B and to copy the results into the File A, if 2 conditions are matched: to have the same week and the same project

For this my intention is to run an index formula with two match criteria: first with search on the row(by the week: e.g. W1 W2 W3.....W52), second with search on the column (by the project name: e.g. eK9, CC21 etc).

I indend to check only those lines from File A which are fullfiling the following 3 conditions:   ws1.Cells(i, 1).Value = "PPS" And ws1.Cells(i, 3).Value = "Projet" And ws1.Cells(i, 6).Value = "Prévisionnel" 

Here is the code I started with. Many thanks for support.

Sub Prev() 

    Dim ws1 As Worksheet: Set ws1 = Workbooks("File A").Worksheets("Sheet1")

    Dim ws2 As Worksheet: Set ws2 = Workbooks("File B").Worksheets("VP")

    Dim lRow As Long, x As Long, lRow2 As Long, i As Long, c As Long

    lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

        For c = 13 To 72

        For i = 9 To lRow

            If ws1.Cells(i, 1).Value = "PPS" And ws1.Cells(i, 3).Value = "Projet" And ws1.Cells(i, 6).Value = "Prévisionnel" Then

                ws1.Cells(i, c) = _

                Application.WorksheetFunction.Index(ws2.Range("D11:BG25"), _

                Aplication.WorksheetFunction.Match(ws1.Cells(6, c), ws2.Range("H11:BG11"), 0) _

                Application.WorksheetFunction.Match(ws1.Cells(i, 5), ws2.Range("F12:F25"), 0))

             End If



End Sub



From a quick glance at your files, I'm not sure how the "expected result" relates to File A. Please edit your original suggestion to show how the values in yellow cells relate.

Also roughly how large is your actual data (rows x columns)?

Are you sure you uploaded the right files? I noite that your above code includes:
but that is not on FileB where the sheet name is FrcstVP  instead.
John_Ru (rep: 2867) Dec 30, '21 at 5:28 pm
Also please use the CODE button when pasting code into your question- it's then easier to read (and otherwise extra Returns are added which divide any split lines if code) 
John_Ru (rep: 2867) Dec 31, '21 at 5:44 am
Hi John_Ru, I renamed the sheet VP instead of FrcstVP and added some more information. Hope is more clear now. Many thanks

PS: was before I have seen your last update
sebas14 (rep: 16) Dec 31, '21 at 9:53 am
Sebas. I'm out and about now so can't so anything on any revised files. If my code worked, please mark by Answer as Selected. If not, please comment but it will be a day or two before I can respond to any major questions. 
John_Ru (rep: 2867) Dec 31, '21 at 10:10 am
Happy New Year!
sebas14 (rep: 16) Dec 31, '21 at 11:08 am
To you too! 
John_Ru (rep: 2867) Dec 31, '21 at 11:38 am
Add to Discussion


Selected Answer


I think I worked out what you were trying to do (and corrected some errors in your files).

Note that you will need to launch both of my revised files below to get the corrected macro to run correctly.

In the revised File A, I've pasted month and week numbers from File B (see green cells) since you had a 60 week year (12 x 5-week months) which confused me when the revised macro gave errors! The yellow cells in BM:BT can be deleted (and you will need to restore month numbers in row 5).

In the revised File B, Module 1 contains the revised macro below (with changes and comments in bold). In that, you will see that the column argument of INDEX is now just the counter c (less 12 so when c=13 Wk1 it refers to the first column Wk1 in the revised range of INDEX). In the worksheet, the cells F20:F21 (in blue) are where the values were "CC21 " (but I changed them to read "CC21" like in File A so a match could be found. The On Error.. statement stops the macro failing where a match is not found (e.g. for "eOV5" from File A").

Sub Prev()

    Dim ws1 As Worksheet: Set ws1 = Workbooks("File A with corrected weeks v0_a.xlsx").Worksheets("Sheet1")
    Dim ws2 As Worksheet: Set ws2 = Workbooks("File B Index Match in VBA v0_a.xlsm").Worksheets("FrcstVP") 'corrected sheet name
    Dim lRow As Long, i As Long, c As Long
    'Dim x As Long, lRow2 As Long ' not used in current macro

    lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

    For i = 9 To lRow 'swapped loops so test is made on row first

        If ws1.Cells(i, 1).Value = "PPS" And ws1.Cells(i, 3).Value = "Projet" And ws1.Cells(i, 6).Value = "Prévisionnel" Then
            For c = 13 To 64 'was to 72 in error (File A had 60 weeks in year (12 x 5-week month)!
                    'added error for when a match of Projet is not made
                    On Error Resume Next
                    'corrected range/cell references
                    ws1.Cells(i, c).Value = _
                    WorksheetFunction.Index(ws2.Range("H12:BG25"), _
                    WorksheetFunction.Match(ws1.Cells(i, 5), ws2.Range("F12:F25"), 0), _
                    c - 12)
             Next c
        End If

    Next i

End Sub
I leave you to clear the used range in File A (before  the macro is next run) and to correct the macro when you use your own files (see the Dim / Set statements above).

Hope this works well for you.



It works great!
There is a difference, indeed, regarding the number of weeks in the files: File A has 60(8 weeks have working days belonging to two different months) and File B has 52 weeks. Could be possible to keep the original format of File A and, in addition, relate somehow the counter c to the month number?

Many thanks!
sebas14 (rep: 16) Dec 31, '21 at 11:08 am
Not sure on odd weeks. I'm nowhere near my PC! May look on Sunday.

Thanks for selecting my answer 
John_Ru (rep: 2867) Dec 31, '21 at 11:42 am
Thinking about it. I suspect it would be quite complicated to code for weeks split over month ends. Why does part of your organisation use working weeks but you want to report calendar months/working days? 
John_Ru (rep: 2867) Dec 31, '21 at 12:07 pm
Depending on departement (sales/financial), reports are using different format.
I think easiest would be to have another row in both reports, with the week number defined like this: W1-1, W2-1, W3-1, W4-1, W5-1,W5-2, W6-2 ... etc

sebas14 (rep: 16) Jan 2, '22 at 1:51 pm
Now running the macro with the original file B, I figure out that a project can be encountered more than once on the rows(File B), is it possible to combine the INDEX MATCH with a SUMIF?

sebas14 (rep: 16) Jan 2, '22 at 5:43 pm
Sebas. I don't know what the numbers mean (if the project appears again). Suggest you ask a new question and explain what you want. 
John_Ru (rep: 2867) Jan 2, '22 at 6:02 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login