macro runtime error

[plost33]

I have the following macro in a spreadsheet in 07 excel:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Not Intersect(Target, Range("D12:D36")) Is Nothing Then
Application.EnableEvents = False
Select Case Target.Value
Case "Weather-NP": Target.Value = "WOW"
Case "Bell Run-NP": Target.Value = "BR"
Case "Survey/Diagnostics-A": Target.Value = "SD-A"
Case "Diamond Wire Cutter-A": Target.Value = "DWC-A"
Case "Diver Survey-A": Target.Value = "DS-A"
Case "Chop Saw-A": Target.Value = "CS-A"
Case "Deck Removal-A": Target.Value = "DR-A"
Case "Structure Removal-A": Target.Value = "SR-A"
Case "Excavate-A": Target.Value = "EXC-A"
Case "Strongback-I": Target.Value = "SB"
Case "Excavate-I": Target.Value = "EXC-I"
Case "Survey/Diagnostic-I": Target.Value = "SD-I"
Case "Annular Interval Tester-I": Target.Value = "IAT-I"
Case "Diver Survey-I": Target.Value = "DS-I"
Case "Conventional Hot Tap-I": Target.Value = "CHT"
Case "Direct Hot Tap-I": Target.Value = "DHT"
Case "Bleed & Kill-I": Target.Value = "BK"
Case "Shear Operation-I": Target.Value = "SO"
Case "Diamond Wire Cutter-I": Target.Value = "DWC-I"
Case "Chop Saw-I": Target.Value = "CS-I"
Case "Porta-Lathe-I": Target.Value = "PL"
Case "Wedding Cake-I": Target.Value = "WC"
Case "Install Well Head-I": Target.Value = "IWH"
Case "Survey/Diagnostic-TA": Target.Value = "SD-TA"
Case "Test Surf/Prod. Annulus-TA": Target.Value = "TCA-TA"
Case "Wireline Bottom-TA": Target.Value = "WL-B"
Case "Establish Injection/Circulation Bottom-TA": Target.Value = "EIR-B"
Case "Perf Bottom-TA": Target.Value = "Perf-B"
Case "Cement Bottom Plug-TA": Target.Value = "CMT-B"
Case "Test CMT Bottom Plug-TA": Target.Value = "TCP-B"
Case "Wireline Intermediate-TA": Target.Value = "WL-I"
Case "Establish Injection/Circulation Intermediate-TA": Target.Value = "EIR-I"
Case "Perf Intermediate-TA": Target.Value = "Perf-I"
Case "Cement Intermediate Plug-TA": Target.Value = "CMT-I"
Case "Test CMT Intermediate Plug-TA": Target.Value = "TCP-I"
Case "Wireline Surface-TA": Target.Value = "WL-S"
Case "Establish Injection/Circulation Surface-TA": Target.Value = "EIR-S"
Case "Perf Surface-TA": Target.Value = "Perf-S"
Case "Cement Surface Plug-TA": Target.Value = "CMT-S"
Case "Test CMT Surface Plug-TA": Target.Value = "TCP-S"
Case "Install Tester-TA": Target.Value = "IAT-TA"
Case "Grout Csg Annulus-TA": Target.Value = "GCA"
Case "Cut & Pull Csg-PA": Target.Value = "CPC"
Case "Debris Clearance-PA": Target.Value = "DC"
Case "Survey/Diagnostic-PA": Target.Value = "SD-PA"
Case "Deck Removal-PA": Target.Value = "DR-PA"
Case "Diver Survey-PA": Target.Value = "DS-PA"
Case "Mesotech-PA": Target.Value = "Meso"
Case "Trawling Site Clearance-PA": Target.Value = "TSC"
Case "Exit Survey-PA": Target.Value = "ES"
Case "Pipeline Survey-PA": Target.Value = "PS"
Case "Mechanical Downtime-NP": Target.Value = "MDT"
Case "Vessel Downtime-NP": Target.Value = "VDT"
Case "Regulatory-NP": Target.Value = "REG"
Case "Mobe/Demobe-NP": Target.Value = "MDM"
Case "Health Safety Environment-NP": Target.Value = "HSE"
Case "Waiting on Orders-NP": Target.Value = "WOO"
Case "Other-NP": Target.Value = "OTHER"
End Select
Application.EnableEvents = True
ElseIf Not Intersect(Target, Range("E12:F36")) Is Nothing Then
Application.EnableEvents = False
Target.Value = WorksheetFunction.MRound(Target.Value, 15 / (60 * 24))
If Target.Column = 6 Then
With Target.Offset(1, -1)
.NumberFormat = "hh:mm"
.Value = Target.Value
End With
End If
Application.EnableEvents = True
End If
End Sub




The Macro works great. But when I save the same excel file into 03 version of Excel, I am getting a run time error.

The message reads: run time error '438': object doesn't support this property or method.

I then have the options of: end, debug, help.

If I select Debug It highlights the row in the macro that reads:

Target.Value = WorksheetFunction.MRound(Target.Value, 15 / (60 * 24))



If anyone can please assist me that would be great. I am not a VBA expert and could use some help.

 

[VoG]

Make sure that the Analysis Toolpak is installed in Excel 2003 - Tools > Add-ins tick Analysis Toolpak.

 

[Richard Schollar]

Hi

You could replace that line with:

Target.Value = WorksheetFunction.Floor(Target.Value+7.5/1440,15/1440)

which will work in xl2007 and xl2003.

 

[plost33]

Vog: Thanks for the help. I made the change and it did help in changing the time in the refrenced cell as desired, but once data is input into the f12 cell I got a diffrent runtime error: it says:

runtime error '1004': unable to set the number format property of the range class.

if I select debug It now highlights the following line:

.NumberFormat = "hh:mm"




any help on this error???

 

[VoG]

What are you actually entering in F12 - number, text, time?

 

[plost33]

time

 

[VoG]

Is the sheet protected?

 

[plost33]

yes it is. but the cells refrenced by that macro are unlocked to allow users to enter in a time.

 

[VoG]

Yes but you are trying to format and write to a cell that is offset from the target. If the offset cell is locked you'll get that error.

 

[plost33]

All cells from e12:f36 are unlocked.