Excel VBA Course
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

Data gathering from a different file with VBA

0

Hi,

I have to Files (attached File A and B) 

For each Project in File A, I need to search for the values in File B - by Project and by Week - and consolidate them into File A. The search for values should be done if it is the same Client and written on the row tagged Prévisionnel

The code I use:

Sub Prev()

    Dim ws1 As Worksheet: Set ws1 = Workbooks("File A.xlsx").Worksheets("1")
    Dim ws2 As Worksheet: Set ws2 = Workbooks("File B.xlsm").Worksheets("1")
    Dim lRow As Long, i As Long, c As Long

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

    For i = 11 To lRow

        If ws1.Cells(i, 1).Value = "PCD" And ws1.Cells(i, 4).Value = "Prévisionnel" Then
            For c = 7 To 66

                    On Error Resume Next

                    ws1.Cells(i, c).Value = _
                    WorksheetFunction.Index(ws2.Range("H11:BG28"), _
                    WorksheetFunction.Match(ws1.Cells(i, 3), ws2.Range("F11:F28"), 0), _
                    c - 7)
             Next c
        End If
    Next i
End Sub

The issue is that a Project can be encounred on multiple rows on File B, but on a single row in File A. The value to be considered,  in this case, should be the sum of values on those rows, by week.

The week number is noted in both Files on row 10 in this format: e.g. W5-1(Week5- January etc) 

Many thanks for any support

excel
MS Office Version: sebas14
posted by: sebas14 (rep: 16) on Mon Jan 10, 2022 at 2:07 pm
Answer
Discuss

Discussion

Sebas

Your .xlsm file shows no code (but this a Q&A forum and in general we expect you to attempt to solve your own problem before asking a specific question here).

Perhaps you have not read the forum's Help section (hyperlink above) but  you will find this sub-section (with the appropriate bullet point in bold below):

Question Etiquette

  * Include all relevant information, sample code, or files.

  * Do not post an entire project and ask someone to do that for you.

  * Do your own research and ask pointed and specific questions.

Please attempt to create the code for your task and upload a revised Excel file / edit your question to raise a specific point (we will be notified when you do so). You might start by viewing Don's tutorial Macro to get Data from Another Workbook in Excel

Thanks in advance
John_Ru (rep: 6142) Jan 10, '22 at 2:21 pm
I have to apologize, I forgot to share the code used and the blocking points
Thank you
sebas14 (rep: 16) Jan 10, '22 at 4:51 pm
I also detailed a little bit more. The current code using Index Match is not able to sum up the values per Project when a Project is encounred on multiple rows on File B. The value to be considered,  in this case, should be the sum of values on the rows for a Project, by week.

Thank you
sebas14 (rep: 16) Jan 11, '22 at 3:53 am
Add to Discussion

Answers

0
Selected Answer

Sebas

Given INDEX/MATCH won't work here, the revised file attached replaces your INDEX/MATCH line with a loop (using new counter variable n) down the corresponding cells of ws2 (offset one column to the left) and adds any numeric values to the variable Total Project if there's a match for the Projet name. Note that the macro works out the last row in FileB and clears the results from File A too. Key bits are in bold below:

Sub Prev()

    Dim ws1 As Worksheet: Set ws1 = Workbooks("File A.xlsx").Worksheets("1")
    Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Worksheets("1") '### avoids needing to change file name
    Dim lRow As Long, i As Long, c As Long
    Dim lRow2 As Long, Projet As String, TotalProjet As Long, n As Long ' added variables

    lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    ws1.Range(Cells(11, 9), Cells(lRow, 68)).ClearContents ' clear results
    'get last row for ws2, column D
    lRow2 = ws2.Cells(Rows.Count, 4).End(xlUp).Row

    For i = 11 To lRow

        If ws1.Cells(i, 2).Value = "PCD" And ws1.Cells(i, 6).Value = "Prévisionnel" Then
            For c = 9 To 68 'corrected column numbers
                On Error Resume Next

                'Get project name and reset total
                Projet = ws1.Cells(i, 5).Value
                TotalProjet = 0
                'Loop down rows of ws2
                For n = 11 To lRow2
                    If ws2.Cells(n, 6).Value = Projet And IsNumeric(ws2.Cells(n, c - 1)) Then
                        TotalProjet = TotalProjet + ws2.Cells(n, c - 1).Value
                    End If
                Next n
                'write to ws1 cell
                ws1.Cells(i, c).Value = TotalProjet

             Next c
        End If
    Next i
End Sub
Hope this fixes it for you.
posted by: John_Ru (rep: 6142) on Tue Jan 11, 2022 at 5:33 am
Discuss

Discussion

Many thanks John_Ru! It works great.
Is it possible to include a condition so that the loop be done like while ws1.Cells(10, c).Value = ws2.Cells(10, c).Value, I mean to avoid be forced having the same number of columns in both files ?
sebas14 (rep: 16) Jan 11, '22 at 7:22 am
Sebas. Actually that file doesn't work so well (I had not spotted that you had different column headings) and puts some results in the wrong columns it seems..

I think you could add another loop to compare column headings from row 10 (as you suggest) so the code becomes that below (I exceeeded the discussion l;imit):
John_Ru (rep: 6142) Jan 11, '22 at 8:04 am 
Sub Prev()
 
    Dim ws1 As Worksheet: Set ws1 = Workbooks("File A.xlsx").Worksheets("1")
    Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Worksheets("1") '### avoids needing to change file name
    Dim lRow As Long, i As Long, c As Long
    Dim lRow2 As Long, ICol2 as Long, Projet As String, TotalProjet As Long, n As Long, m As Long ' added variables
    
    lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    ws1.Range(Cells(11, 9), Cells(lRow, 68)).ClearContents ' clear results
    'get last row for ws2 (column D) AND last column (row 10, month-week number)
    lRow2 = ws2.Cells(Rows.Count, 4).End(xlUp).Row
    lCol2 = Cells(10, Columns.Count).End(xlToLeft).Column
    For i = 11 To lRow
 
        If ws1.Cells(i, 2).Value = "PCD" And ws1.Cells(i, 6).Value = "Prévisionnel" Then
            For c = 9 To 68 'corrected column numbers
                On Error Resume Next
...  
John_Ru (rep: 6142) Jan 11, '22 at 8:07 am 
            
                'Get project name and reset total
                Projet = ws1.Cells(i, 5).Value
                TotalProjet = 0
                'Loop along columns of ws2
                For m = 8 To lCol2
                    If ws2.Cells(10, m).Value = ws1.Cells(10, c).Value Then
                        'Loop down rows of ws2
                        For n = 11 To lRow2
                            If ws2.Cells(n, 6).Value = Projet And IsNumeric(ws2.Cells(n, m)) Then
                                TotalProjet = TotalProjet + ws2.Cells(n, m).Value
                            End If
                        Next n
                        'write to ws1 cell
                        ws1.Cells(i, c).Value = TotalProjet
                    End If
                Next m
             Next c
        End If
    Next i
End Sub
Please try this (I did by deleting February from File B (and nothing appears in File A- this might be a problem). Maybe a rethink/ new approach is needed.
John_Ru (rep: 6142) Jan 11, '22 at 8:08 am
For me works phenomenal so far, it's exactly what I've expected
Kudos John_Ru
sebas14 (rep: 16) Jan 12, '22 at 7:38 am
Okay Sebas but the two versions produce slight different results as I recall. Answer vesrion of File B gives  results on File A of 25 for D41 under week W5-2 and 50 in W6-2. In the modified macro (split above), those values appear under W6-2 and  W7-2 instead. I leave you the check.
John_Ru (rep: 6142) Jan 12, '22 at 8:02 am
Add to Discussion


Answer the Question

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