stronik814
New Member
- Joined
- Jan 22, 2014
- Messages
- 5
I need to do the following but I'm a complete Excel novice when it comes to macros. I know the logic but don't know the syntax to write the macro. I was wondering if someone would be able to help me out.
-Thank you!
I'm trying to calculate Variable A for M1, the # of days since the last audit. Looking at the table below, from current date 11/7, the last audit was performed on 11/1. So 6 days since the last audit, so A = 6.
To do this, I was thinking of the following logic.
Search for today's date in column B.
Once found, set that variable to Q.
After, go over 2 columns right and select that value, let's say x.
If(x>=0, A = 0, else do the following: go up column D until x does not = blank.
Once x does not equal blank, go over 2 columns left and select that value.
Set that variable to W.
A = Q - W.
[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Today's Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/7/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 = No Leak[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1 = Leaks[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]# of days[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]M1[/TD]
[TD]Variable A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]M2[/TD]
[TD]Variable B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]M3[/TD]
[TD]Variable C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]M4[/TD]
[TD]Variable D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]M1 Leak?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]10/31/14[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]11/1/14[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]11/2/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD]11/3/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD]11/4/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD]11/5/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD]11/6/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD]11/7/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
-Thank you!
I'm trying to calculate Variable A for M1, the # of days since the last audit. Looking at the table below, from current date 11/7, the last audit was performed on 11/1. So 6 days since the last audit, so A = 6.
To do this, I was thinking of the following logic.
Search for today's date in column B.
Once found, set that variable to Q.
After, go over 2 columns right and select that value, let's say x.
If(x>=0, A = 0, else do the following: go up column D until x does not = blank.
Once x does not equal blank, go over 2 columns left and select that value.
Set that variable to W.
A = Q - W.
[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Today's Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/7/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 = No Leak[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1 = Leaks[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]# of days[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]M1[/TD]
[TD]Variable A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]M2[/TD]
[TD]Variable B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]M3[/TD]
[TD]Variable C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]M4[/TD]
[TD]Variable D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]M1 Leak?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]10/31/14[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]11/1/14[/TD]
[TD][/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]11/2/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD]11/3/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD]11/4/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD]11/5/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD]11/6/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD]11/7/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]