Can't get nested Loop to copy range values from 2 tables working correctly

[gtol]

Hi,

I am working on a VBA application and I have encountered a situation that needs a nested loop to copy values from 2 tables (Departments and Roles) from a Source sheet to a Destination sheet.

The copy operation needs to show all possible combinations of Department and Role that can exist as illustrated below :

Departments
A
B
C

Roles
1
2
3

Required result (9 possible combinations):

A1
A2
A3
B1
B2
B3
C1
C2
C3

I know we will need a nested loop here that goes through both tables sequentially using an inner and outer loop but I can't figure out the correct syntax to get this working. I have seached the forum but the examles I found were very complex and I couldn't amend the code to achieve the desired result without getting lots of errors.

I am attaching a spreradsheet to better illustrate what is required. I have also created the bare bones of a procedure for the loop.

I am hoping that there is some VBA guru out there who can help !!

Many thanks in advance,


GTOL

[StephenR]

Using your framework:

Sub TestLoop()

   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
      
   Dim Depts As Range
   Dim Roles As Range
   
   Dim CurCell As Range
   Dim DestCell As Range
    
   Dim i As Integer
   Dim j As Long
   Dim k As Long
   Dim NumRows As Integer
         
         
   Set Ws1 = Worksheets("Source")
   Set Ws2 = Worksheets("Destination")
   Set Depts = Range("Departments")
   Set Roles = Range("Roles")
         
   
   Set DestCell = Range("B6")
   NumRows = Depts.Rows.Count
   
   
   Application.ScreenUpdating = False
          
  'starting position
    
    For i = 1 To NumRows
        For j = 1 To Roles.Count
            Ws2.Range("A1").Offset(k) = Depts(i)
            Ws2.Range("A1").Offset(k, 1) = Roles(j)
            k = k + 1
        Next j
    Next i
    
   Application.ScreenUpdating = True
       
End Sub

[gtol]

Hi StephenR from London

What can I say ???
That works perfectly and I can apply this to my application.
Very neat and tidy code indeed.

Thanks a million !!!


GTOL