VBA to loop thru column and copy/paste based on cell criteria.

[BPW]

I'm stuck on some VBA and was wondering if someone could help.

I have a column that I would like a macro to step through and:

• Find an employee's K ID, in Col A, which always begins with a K but is variable in the number of trailing numeric digits, between 6-7.
  • If found, copy and paste into the same row, Col C.
  • Copy the name below the K ID in Col A and paste directly beside the K ID in the above row, Col D.
• Loop until first empty cell in Col A.

View attachment 73400

Thanks for your help!

 

[Joe4]

Do you really need VBA?
This can be done with a few simple formulas.

Formula in cell C1:

=IF(AND(LEN(A1)>=7,LEN(A1)<=8,LEFT(A1)="K"),A1,"")

Formula in cell D1:

=IF(LEFT(C1,1)="K",A2,"")

And then copy those two formulas down to your last row of data.

 

[BPW]

Very creative! This works perfectly and much simpler than the original approach. Thank you Joe4!

 

[Joe4]

You are welcome!
Glad I was able to help.