• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 41
  • Last Modified:

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
0
Manju
Asked:
Manju
  • 4
  • 3
  • 3
  • +1
1 Solution
 
NorieVBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
So how can it be determined what data goes where on the 'Final' sheet?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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
 
NorieVBA ExpertCommented:
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
 
ManjuIT - Project ManagerAuthor Commented:
Perfect
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now