Whats error messgae '400' mean?

[Steve M]

and why does this script make it happen please

Sub MergeMove()
'
' MergeMove Macro

'
With ActiveSheet
xlastrow = .Cells(Rows.Count, 2).End(xlUp).Row
For x = 1 To xlastrow
.Cells(x, 15) = .Cells(x, 8) & " " & .Cells(x, 9) & " "
Next x
End With
'
Columns("O:O").Select
Selection.Copy
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("N:N").Select
Selection.Delete Shift:=xlToLeft
Range("H1").Select
ActiveCell.FormulaR1C1 = "Address Ln.1"
Range("H2").Select
'

Columns("G:G").Select
Selection.NumberFormat = "yyyy-mm-dd"
Columns("D:D").Select
Selection.Replace What:="", Replacement:="N/A", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns("H:H").Select
Selection.Replace What:="", Replacement:="N/A", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns("I:I").Select
Selection.Replace What:="", Replacement:="N/A", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="ns_no", Replacement:="NS No.", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="surname", Replacement:="Surname", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="forename1", Replacement:="Forename 1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="forename2", Replacement:="Forename 2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="title", Replacement:="Title", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="***", Replacement:="***", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="dob", Replacement:="DOB", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="house_no", Replacement:="Address Ln.1", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="road_name", Replacement:="Address Ln.2", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="town_name", Replacement:="Town", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Rows("1:1").Select
Selection.Replace What:="postcode", Replacement:="Post Code", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Columns("M:M").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="N/A,P,L,D"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = "P=Newly found" & Chr(10) & "L=Left" & Chr(10) & "D=Died"
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("L1").Select
ActiveCell.FormulaR1C1 = "PCode"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Registration Details"
Cells.Select
Selection.Columns.AutoFit
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
ActiveWindow.SmallScroll ToRight:=3
Range("M1").Select
Columns("M:M").ColumnWidth = 9.86
Columns("M:M").ColumnWidth = 10.86
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("L:L").Select
Selection.Replace What:="", Replacement:="N8???", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A1").Select
Columns("O:O").Select
Selection.Copy
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("N:N").Select
Selection.Delete Shift:=xlToLeft
Range("H1").Select
ActiveCell.FormulaR1C1 = "Address Ln.1"
Range("H2").Select
End Sub

[Steve M]

Forgot to say that this macro is ran on data on another workbook not on the one its in

[broro183]

Hi Steve,

If the active sheet is the one that you want to be changed when the macro is run then it doesn't/shouldn't matter which workbook the code is in.

To find out what the error code "400" means, run the following sub:
Sub testingerror()
MsgBox Error(400)
End Sub

Although, for you to see this error, excel is probably asking if you want to debug the code. If it does, what line is highlighted in yellow when you choose "debug"?

Also, as this code has been created by the macro recorder it is relatively ineffiecient b/c it "selects" a lot more than is required & could be tidied up a lot eg
Columns("O:O").Select
Selection.Copy
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

can become:
Columns("O:O").Copy
Columns("H:H").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:= False, Transpose:=False

&:
*any lines in the "with" sections that end in "= false" can probably be deleted.
*try changing all the lines refering to "rows("1:1").select can be changed to be included in a "with" construct eg

with Rows("1:1")
.Replace What:="dob", Replacement:="DOB", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="surname", Replacement:="Surname", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
'...
end with

(This removes the need for every second line to be "Rows("1:1").select".)


I'm off to bed now but will have another look tomorrow to see if I can figure out what is causing your error (& may post a tidied version of your code).

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

[Tom Ogilvy]

My experience with the 400 error is that it is intermittent and not
necessarily associated with something in the macro. I would close excel and
reopen it and see if goes away.

--
Regards,
Tom Ogilvy


"Steve M" wrote:

>
> Forgot to say that this macro is ran on data on another workbook not on
> the one its in
>
>
> --
> Steve M
> ------------------------------------------------------------------------
> Steve M's Profile: http://www.excelforum.com/member.php...o&userid=32520
> View this thread: http://www.excelforum.com/showthread...hreadid=524276
>
>