Merge two excel macro

Question

I need to merge 2 macro & i tried the same,but end of the first macro it's shows error.Pls help in this regards,

Macro

Sub ERS()
'
' ERS Macro
'

'
    ActiveWindow.SmallScroll Down:=-6
    Range("E1:E41").Select
    Selection.End(xlUp).Select
    Range("E2:E100").Select
    Selection.TextToColumns Destination:=Range("E2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1)), _
        TrailingMinusNumbers:=True
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-4]"
    Range("P2").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("P2:P100"), Type:=xlFillDefault
    Range("P2:P100").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-21
    Selection.Replace What:="\", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveWindow.SmallScroll Down:=-3


    Windows("qty.xls").Activate
    ActiveWindow.SmallScroll Down:=-12
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-9],lots.xlsx!R2C2:R100C16,15,0)"
    Range("M2").Select
    Selection.Copy
    Range("N2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-6]"
    Range("M2:N2").Select
    Selection.Copy
    Range("M2:N100").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-12
End Sub

I don't see any syntax error in your code. Can you please specify `it's shows error`? — rollstuhlfahrer, Aug 17 '12 at 08:41

score 0 · Answer 1 · edited May 23 '17 at 10:34

Try this code (UNTESTED)

Let me know if you get any errors. Also mention the error message and the line which gives you the error

Sub ERS()
    Dim lRow As Long

    With Thisworkbook.ActiveSheet
        lRow = .Range("E" & .Rows.Count).End(xlUp).Row

        .Range("E2:E" & lRow).TextToColumns Destination:=.Range("E2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1)), _
        TrailingMinusNumbers:=True

        With .Range("P2:P" & lRow)
            .FormulaR1C1 = "=RC[-5]&RC[-4]"
            .Value = .Value

            .Replace What:="\", Replacement:=".", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        End With
    End With

    Windows("qty.xls").Activate
    Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-9],lots.xlsx!R2C2:R100C16,15,0)"
    Range("N2").FormulaR1C1 = "=RC[-1]*RC[-6]"
    Range("M2:N2").Copy Range("M2:N100")
End Sub

I would also recommend you to read this. This exactly shows the reason why you should be avoiding the use of .Select

I execute the above code,but it shows run time error'9'.Pls do the needful. — user1606366, Aug 17 '12 at 13:00

Merge two excel macro

1 Answers1