Log In
Register
Home
Active Member
June 21, 2022
Offline
Hi Excel experts ,kindly help to advise on this . For example , I have five names from other source and I wanted to return those name in sequence like in destination tab .(2nd picture below)
Below is my code in VBA , my problem can be solved by doing even and odd rows return if there's only two names . ( Please refer to 1st picture ) . But let's say I have five names or even more , but I wanted to return those name in sequence , how should I do it . ( 2nd picture ) Thanks in advance .
Sub AssignAgent()
Dim r As range
Dim i As Integer
For Each r In range("AE7:AE1000") 'Change this range
i = r.Row
If i Mod 2 = 1 Then 'this checks to see if i is odd
r.Cells.Value = "Mages"
Else
r.Cells.Value = "Yani"
End If
Next r
End Sub
See Answer
November 8, 2013
Offline
Hi Chris,
Try this one:
Sub AssignAgent()
Dim r As range
Dim i As Integer
Dim Agents As Range: Set Agents=ThisWorkbook.Worksheet("Agents").Range("A1:A5")
Dim Increment as Byte
Increment=1
For Each r In range("AE7:AE1000") 'Change this range
r.Cells.Value = Agents.Cells(Increment)
Increment=Increment+1
If Increment>Agents.Cells.Count then Increment=1 'go back to the first, reached the end of range
Next r
End Sub
Answers Post
1 Guest(s)
