Manju
asked on
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
"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
How do the dates on 'Calendar' tie in with the data on 'Resources'?
ASKER
It doesnt tie at all.
Calendar is independent of resources.
Calendar is independent of resources.
So how can it be determined what data goes where on the 'Final' sheet?
ASKER
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.
and Resources are 3 rows
Final should be 3 * 31
For each date, 3 resources & so on.
Without relationship between ressources and dates, there isn't much we can do, beside using the CONCATENATE function.
Manju
Perhaps some sample before/after data might help clarify things, could you attach a sample workbook?
Perhaps some sample before/after data might help clarify things, could you attach a sample workbook?
I think it's pretty simple
Here's an example
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
Just to see how it works here's the excel file with the macro in example
ExEx.xlsm
ExEx.xlsm
ASKER
@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
Test_loop.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect