A
ADE2
Hi
I have A macro, shown below,which works well.I now need to add som
additional features.
ORIGINAL MACRO:-
Private Sub Worksheet_Change(ByVal Target As Range)
If InStr(1, Range("T1").Value, "/") > 0 Then
Range("Z10:AE13,Z21:AD25,AF31:AF39,AK4:AK43,AN4:AN43,AE6,AE17,AF7,AF9,AF11").NumberForma
= "0.0000"
Else
Range("Z10:AE13,Z21:AD25,AF31:AF39,AK4:AK43,AN4:AN43,AE6,AE17,AF7,AF9,AF11").NumberForma
= "0.000"
End If
End Sub
As you will see the macro is based upon the appearance of a slash o
not in cell T1.
I have date formulas in four different cells(Y22,Y23,Y24,Y25) whic
need to alter depending upon the appearance of a slash or not in cel
T1.
So if cell T1 does NOT show a slash :
Y22 =WORKDAY(Y10,6-WEEKDAY(Y10))
Y2
=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5))
Y24 =DATE(YEAR(AC2),12,31)+5-MAX(5,WEEKDAY(DATE(YEAR(AC2),12,31),2))
Y2
=DATE(INT(YEAR(AC2)/10)*10+9,12,31)+5-MAX(5,WEEKDAY(DATE(INT(YEAR(AC2)/10)*10+9,12,31),2))
If T1 does show a slash:
Y22 =V10-WEEKDAY(V10,2)+8
Y23 =WORKDAY(EOMONTH(V10,0),1)
Y24 =WORKDAY(EOMONTH(V10,12-MONTH(V10)),1)
Y25 =WORKDAY(EOMONTH(V10,12-MONTH(V10)+12*(9-MOD(YEAR(V10),10))),1)
Any help is greatly appreciated
Ad
I have A macro, shown below,which works well.I now need to add som
additional features.
ORIGINAL MACRO:-
Private Sub Worksheet_Change(ByVal Target As Range)
If InStr(1, Range("T1").Value, "/") > 0 Then
Range("Z10:AE13,Z21:AD25,AF31:AF39,AK4:AK43,AN4:AN43,AE6,AE17,AF7,AF9,AF11").NumberForma
= "0.0000"
Else
Range("Z10:AE13,Z21:AD25,AF31:AF39,AK4:AK43,AN4:AN43,AE6,AE17,AF7,AF9,AF11").NumberForma
= "0.000"
End If
End Sub
As you will see the macro is based upon the appearance of a slash o
not in cell T1.
I have date formulas in four different cells(Y22,Y23,Y24,Y25) whic
need to alter depending upon the appearance of a slash or not in cel
T1.
So if cell T1 does NOT show a slash :
Y22 =WORKDAY(Y10,6-WEEKDAY(Y10))
Y2
=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5))
Y24 =DATE(YEAR(AC2),12,31)+5-MAX(5,WEEKDAY(DATE(YEAR(AC2),12,31),2))
Y2
=DATE(INT(YEAR(AC2)/10)*10+9,12,31)+5-MAX(5,WEEKDAY(DATE(INT(YEAR(AC2)/10)*10+9,12,31),2))
If T1 does show a slash:
Y22 =V10-WEEKDAY(V10,2)+8
Y23 =WORKDAY(EOMONTH(V10,0),1)
Y24 =WORKDAY(EOMONTH(V10,12-MONTH(V10)),1)
Y25 =WORKDAY(EOMONTH(V10,12-MONTH(V10)+12*(9-MOD(YEAR(V10),10))),1)
Any help is greatly appreciated
Ad