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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
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)
