Please help! I don't even know where to start.

[ntupper]

Hi, I am fairly new to, and have a limited knowledge of VBA and would appreciate if someone could point me in the right direction to get me started with some code that I need to write.

I have a list of usernames (see below example Pre-macro data) and a list of desks that the user will be going from and to (the list could be 500+ rows). Invariably the list will work out in to some kind of rotational order and will look something like the example below (Post macro).

I have tried using macro recorder to get me started but am now begining to get very frustrated due to my inexperience and ultimate lack of knowledge of VBA . Any help would be greatly appreciated.

Thanks,
Neil.

[ntupper]

.

[Bob Phillips]

Your problem needs a lot more explanation if you want any help.

[ntupper]

Apologies I should have been more descriptive. In Excel 2003, I am trying to sort the data using a macro from the original list (example pre-macro) in to multiple lists that will look like the post macro example. The multiple list criteria will be that the from desk (column B) in the second row of data will be the to desk (column C) from the first row of data. Then the from desk in the third row will be the same as the to desk from the second and so on until it loops back to the from desk on the first row of data creating a full rotation. The macro will then need to keep running through the pre-macro list creating however many rotational lists until it hits the last row of data on the pre-macro list.

Neil.

[lucas]

What determines how many names are used in rotation 1?

[ntupper]

There really is no constant as this could change every time it is used. It will depend on how many times the from desk cell matches the to desk cell on the previous row - it could be never, once or multiple times - until a to desk cell on any given row matches the first from desk cell.

[ntupper]

Steve, to expand on my previous reply the macro would need to loop until a value in column C matches the value in cell B3 in my example.

Neil.

[ntupper]

OK, I have recorded a macro that runs the very first part of my requirement, though it is quite clumsy because of recording vlookups etc......


Range("A1:C1").Select
Selection.Copy
Range("H1").Select
ActiveSheet.Paste
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D500")
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[-4]"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E500")
Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-7]"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=RC[-7]"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RC[-7]"
Range("H3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],C[-6]:C[-3],4,FALSE)"
Range("I3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],C[-6]:C[-4],2,FALSE)"
Range("J3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-1]C,C[-8]:C[-5],2,FALSE)"
Range("H3:J3").Select
Selection.AutoFill Destination:=Range("H3:J8"), Type:=xlFillDefault
Range("H3:J8").Select

The code in red is where I need to add a loop to continue filling the subsequent rows until a value in column "J" matches that of cell "I2", now I'm stuck again!, but I think this may help you to help me as I am probably not the best at explaining what I am trying to do!!!!

[georgiboy]

This is quite clumsy, but its the best i could do :)

It might get you on your way with completing this task...

[ntupper]

Thanks Georgiboy, that works a treat and helps me a great deal. :)