Hi everyone, have a situation that I'm wondering how to loop the action to save me some time.
I have 340 assignments, every 3 cells is grouped into a new assignment. Assignment1= Columns B,C,D Assignment2=E,F,G
Assignment3=H,I,J
My issue is that I need to copy/paste the formulas for B5,5C all the way across for 340 assignments but B5 doesn't keep adding the row # because I'm pasting horizontal across and it's a locked reference. My formula in C5 is a date formula that pops the current day whenever an analyst answers a Yes, No, or N/A in B6, E6, H6, etc. C5 works but I'd have to do it by hand for 340 assignments.
What I'm doing now is I built out 20 assignments that are correct order and formulas, I copy/paste them across and then use a macro that finds/replaces to the correct locked cell reference from the Annie sheet, but this takes a lot of manual labor and can lead to mistakes.
I'm a beginner in using VBA for loops but I'm wondering if this is a situation I could start to develop one? Any help on this would be great! Thanks!
<TBODY>
</TBODY>
I have 340 assignments, every 3 cells is grouped into a new assignment. Assignment1= Columns B,C,D Assignment2=E,F,G
Assignment3=H,I,J
My issue is that I need to copy/paste the formulas for B5,5C all the way across for 340 assignments but B5 doesn't keep adding the row # because I'm pasting horizontal across and it's a locked reference. My formula in C5 is a date formula that pops the current day whenever an analyst answers a Yes, No, or N/A in B6, E6, H6, etc. C5 works but I'd have to do it by hand for 340 assignments.
What I'm doing now is I built out 20 assignments that are correct order and formulas, I copy/paste them across and then use a macro that finds/replaces to the correct locked cell reference from the Annie sheet, but this takes a lot of manual labor and can lead to mistakes.
I'm a beginner in using VBA for loops but I'm wondering if this is a situation I could start to develop one? Any help on this would be great! Thanks!
B | C | D | E | F | G | H | I | J | |
4 | PROC ID | DATE | ANALYST | PROC ID | DATE | ANALYST | PROC ID | DATE | ANALYST |
5 | =Annie'!$D$3 | =IF(B6<>"", IF(B5<>0, NOW(), ""), "") | Annie C. | =Annie'!$D$4 | =IF(E6<>"", IF(E5<>0, NOW(), ""), "") | Annie C. | =Annie'!$D$5 | =IF(H6<>"", IF(H5<>0, NOW(), ""), "") | Annie C |
6 | Yes | No | N/A |
<TBODY>
</TBODY>