Excel Macro - Loop Help

Experts - I have an excel workbook with 3 sheets.

"Calendar"
"Resources"
"Final"

"Calendar" - This sheet will have current month dates ("Date, Day, Month, Week, Qtr etc") Ex: Current month will have 1st Jan to 31st Jan data. Similarly every new month, data here gets refreshed.
"Resources" - This will have EmpID & Name - Constant data

What I need is...,
in "Final" sheet, For Each date in the calendar, list all resources details
Ex:
Calendar has
1/1/18
1/2/18
....
1/31/18

Resources has
123 ABC
122 CCC
111 BBB

Final Should have
1/1/18 123 ABC
1/1/18 122 CCC
1/1/18 111 BBB
....
and so on till 31st Jan.

Kindly help me with a macro
LVL 7
ManjuIT - Project ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
How do the dates on 'Calendar' tie in with the data on 'Resources'?
0
ManjuIT - Project ManagerAuthor Commented:
It doesnt tie at all.

Calendar is independent of resources.
0
NorieAnalyst Assistant Commented:
So how can it be determined what data goes where on the 'Final' sheet?
0
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

ManjuIT - Project ManagerAuthor Commented:
I mean, lets say Calendar has 1 to 31 dates in rows
and Resources are 3 rows

Final should be 3 * 31
For each date, 3 resources & so on.
0
Fabrice LambertFabrice LambertCommented:
Without relationship between ressources and dates, there isn't much we can do, beside using the CONCATENATE function.
0
NorieAnalyst Assistant Commented:
Manju

Perhaps some sample before/after data might help clarify things, could you attach a sample workbook?
0
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
I think it's pretty simple
Here's an example
Sub Final()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim r As Range
    Dim r1 As Range
    Dim i1, i2, ifinal As Integer
    
    Set ws1 = ThisWorkbook.Worksheets("Calendar")
    Set ws2 = ThisWorkbook.Worksheets("Resources")
    Set ws3 = ThisWorkbook.Worksheets("Final")
  
    For Each r In ws1.UsedRange.Rows
        i1 = i1 + 1
        i2 = 0
        For Each r1 In ws2.UsedRange.Rows
            i2 = i2 + 1
            ifinal = ifinal + 1
            ws1.Range("A" & i1).Copy ws3.Range("A" & ifinal)
            ws2.Range("A" & i2).Copy ws3.Range("B" & ifinal)
            ws2.Range("B" & i2).Copy ws3.Range("C" & ifinal)
        Next
    Next

End Sub

Open in new window

0
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
Just to see how it works here's the excel file with the macro in example
ExEx.xlsm
0
ManjuIT - Project ManagerAuthor Commented:
@Ferruccio Accalai - Pretty near. I've attached a sample file. Your query is behaving erratic with different columns being pulled. Can you check the attachment & modify your code please?
Test_loop.xlsm
0
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
Sorry for the delay, lunch time here ;-)

Here's your adapted Macro. Enjoy it
Sub Final()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim r As Range
    Dim r1 As Range
    Dim i1, i2, ifinal As Integer
    
    Set ws1 = ThisWorkbook.Worksheets("Calendar")
    Set ws2 = ThisWorkbook.Worksheets("Resources")
    Set ws3 = ThisWorkbook.Worksheets("Final")
    i1 = 1
    ifinal = 1
    
    For Each r In ws1.UsedRange.Rows
        i1 = i1 + 1
        i2 = 1
        If i1 <= ws1.UsedRange.Rows.Count Then
        For Each r1 In ws2.UsedRange.Rows
            i2 = i2 + 1
            If i2 <= ws2.UsedRange.Rows.Count Then
                ifinal = ifinal + 1
                ws1.Range("A" & i1).Copy ws3.Range("A" & ifinal)
                ws2.Range("A" & i2).Copy ws3.Range("B" & ifinal)
                ws2.Range("B" & i2).Copy ws3.Range("C" & ifinal)
            End If
        Next
        End If
    Next

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ManjuIT - Project ManagerAuthor Commented:
Perfect
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.