Worksheet Event Change Macro

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
 
F

Frank Kabel

Hi
try something like the following:

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,A
F11").NumberFormat
= "0.0000"
Range("Y22").formula = "=V10-WEEKDAY(V10,2)+8"
'apply the other formulas

Else

Range("Z10:AE13,Z21:AD25,AF31:AF39,AK4:AK43,AN4:AN43,AE6,AE17,AF7,AF9,A
F11").NumberFormat
= "0.000"
Range("Y22").formula = "=WORKDAY(Y10,6-WEEKDAY(Y10))"
'apply the other formulas
End If
End Sub
 
B

Bob Phillips

I haven't tested this, just cut it according to your explanation.

Your example uses the WORKDAY function. As the WORKDAY function is part of
the Analysis Tool Pack add-in, you need to enaure that the ATP-VBA add-in is
loaded (probably so in your case). In Excel, go to the Tools menu, choose
Add-Ins, then put a check next to "Analysis Tool Pak - VBA" item. Click OK.
Then, go in to VBA, go to the Tools>References menu, select ATPVBAEN.xla,
and clik OK.


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")
_
.NumberFormat = "0.0000"
Range("Y22").Value = Range("V10").Value -
Weekday(Range("V10").Value, 2) + 8
Range("Y23").Value = WORKDAY(EOMONTH(Range("V10").Value, 0), 1)
Range("Y24").Value = WORKDAY(EOMONTH(Range("V10").Value, 12 - _
Month(Range("V10").Value)), 1)
Range("Y25").Value = WORKDAY(EOMONTH(Range("V10").Value, , 12 - _
Month(Range("V10").Value) + 12 * (9 - (Year(Range("V10").Value)
Mod 10))), 1)
Else

Range("Z10:AE13,Z21:AD25,AF31:AF39,AK4:AK43,AN4:AN43,AE6,AE17,AF7,AF9,AF11")
_
.NumberFormat = "0.000"
Range("Y22").Value = WORKDAY(Range("Y10"), 6 -
Weekday(Range("Y10")))
Range("Y23").Value = _
DateSerial(Year(Range("A1")), Month(Range("A1")) + 1, 0) - _
(Max(0, Weekday(DateSerial(Year(Range("A1")), _
Month(Range("A1")) + 1, 0), 2) - 5))
Range("Y24").Value = _
DateSerial(Year(Range("AC2")), 12, 31) + 5 - _
Max(5, Weekday(DateSerial(Year(Range("AC2")), 12, 31), 2))

Range("Y25").Value = _
DateSerial(Int(Year(Range("AC2")) / 10) * 10 + 9, 12, 31) + 5 -
_
Max(5, Weekday(DateSerial(Int(Year(Range("AC2")) / 10) * 10 + 9,
12, 31), 2))
End If

End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

cop-out<vbg>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top