Runtime Error 1004: --> object defined error

[erniepoe]

Hi all, I'm getting runtime error 1004 in my VBA code, and I'm wondering if any of you can spot any obvious mistakes in it. I've been racking my brain and trying google searches, but with no luck. Anybody see why I might be getting that error?


Thanks,
Ernie







Sub FormatPipeline()
' FormatPipeline Macro

ActiveSheet.Unprotect Password:="Thirdparty"

Columns("K:K").Select
Selection.Insert Shift:=xlToRight
Columns("M:M").Select
Selection.Insert Shift:=xlToRight
Columns("O:O").Select
Selection.Insert Shift:=xlToRight
Columns("Q:Q").Select
Selection.Insert Shift:=xlToRight
Columns("S:S").Select
Selection.Insert Shift:=xlToRight
Columns("U:U").Select
Selection.Insert Shift:=xlToRight

Range("K:K,O:O,Q:Q,S:S,U:U,M:M").Select

With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=""Legacy"",""Legacy"",IF(RC[-1]="""","""",DATE(YEAR(RC[-1]),MONTH(RC[-1]),1)))"
Range("K2").Select
Selection.Copy
Range("K3:K500").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("K2:K500").Select
Selection.Copy

Range("M2,O2,Q2,S2,U2").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Range("K:K,M:M,O:O,Q:Q,S:S,U:U").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

Range("C:C,F:F").Select
Selection.NumberFormat = "#,##0.00"
Range("D:D,G:G").Select
Selection.NumberFormat = "#,##0"
Range("E:E,H:H").Select
Selection.NumberFormat = "#,##0.000"
Columns("P:Y").Select
Selection.NumberFormat = "General"
Columns("Z:AC").Select
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
Columns("AD:AD").Select
Selection.NumberFormat = "General"
Columns("AE:AH").Select
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

Range("J:O").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=InvDate"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Rows("1:1").Select
Selection.NumberFormat = "General"
Rows("1:1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Application.ReplaceFormat.NumberFormat = "General"
Cells.Replace What:="Legacy", Replacement:="Legacy", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True

Range("A1").Select

ActiveSheet.Protect Password:="Thirdparty"

MsgBox "Finished Formatting"
End Sub

 

[erniepoe]

When I hit debug, it highlights the following code for me. Anybody see what might be wrong with this?





.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=InvDate"

 

[shg]

Is InvDate a valid named range with Workbook scope, or with worksheet scope for the worksheet where you are applying the validation?

 

[erniepoe]

Ha, i'm an idiot. the named range was InvMonth, not InvDate. I am a fool, and now the code works fine.

Thanks for your help, however.