+ Reply to Thread
Results 1 to 2 of 2

Run time error 9 while running on other computers

  1. #1
    Registered User
    Join Date
    02-28-2015
    Location
    Al Khobar
    MS-Off Ver
    Microsoft 2007
    Posts
    0

    Run time error 9 while running on other computers

    Getting run time error 9 when running on other PC's

    Private Sub CommandButton1_Click()
    Dim x As Integer
    Dim y As Integer
    Dim a As Integer
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim m As Integer
    Dim n As Integer
    Dim RNG As Range
    Dim LRow As Integer
    Dim ILRow As Integer
    Dim xPath As String
    Dim Ws As Worksheet
    Dim Wb As Workbook
    Dim FName As String
    Dim FIsm As String



    Range("A2:A2000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("O1" _
    ), UNIQUE:=True
    Worksheets("INPUT").Range("O1:O2000").Copy
    Worksheets("UNIQUE").Range("A1:A2000").PasteSpecial
    Worksheets("UNIQUE").Range("A2:A2000").Copy
    Worksheets("UNIQUE").Range("A1:A1999").PasteSpecial
    ILRow = Worksheets("INPUT").Cells(Worksheets("INPUT").Rows.Count, "A").End(xlUp).Row
    Worksheets("INPUT").Range("O1:O" & ILRow).Clear
    Sheets("INPUT").Activate
    With ActiveSheet
    x = .Cells(.Rows.Count, "C").End(xlUp).Row
    End With
    xPath = Application.ActiveWorkbook.Path
    'Copying values from main input to MTO sheet
    y = WorksheetFunction.CountA(Worksheets("UNIQUE").Range("A1:A2000")) 'counting numbers of non empty cells in range
    Worksheets("UNIQUE").Range("F1") = x
    Worksheets("UNIQUE").Range("F2") = y
    For j = 1 To y
    Worksheets("MTO_T").Range("A1:E500").Clear
    'ILRow = Worksheets("INPUT").Cells(Worksheets("INPUT").Rows.Count, "A").End(xlUp).Row
    a = 2
    For i = 2 To x
    If Worksheets("INPUT").Range("A" & i) = Worksheets("UNIQUE").Range("A" & j) Then
    Worksheets("MTO_T").Range("A" & i).Value = Application.WorksheetFunction.VLookup(Worksheets("UNIQUE").Range("A" & j), Worksheets("INPUT").Range("A" & (a) & ":K" & ILRow), 11, False)
    Worksheets("MTO_T").Range("b" & i).Value = Application.WorksheetFunction.VLookup(Worksheets("UNIQUE").Range("a" & j), Worksheets("INPUT").Range("A" & (a) & ":K" & ILRow), 8, False)
    Worksheets("MTO_T").Range("c" & i).Value = Application.WorksheetFunction.VLookup(Worksheets("UNIQUE").Range("a" & j), Worksheets("INPUT").Range("A" & (a) & ":K" & ILRow), 9, False)

    End If
    a = a + 1
    Next i
    Worksheets("MTO_T").Activate
    LRow = Worksheets("MTO_T").Cells(Worksheets("MTO_T").Rows.Count, "A").End(xlUp).Row
    Set RNG = Worksheets("MTO_T").Range("A1:A" & LRow).SpecialCells(xlCellTypeBlanks)
    RNG.EntireRow.Delete
    LRow = Worksheets("MTO_T").Cells(Worksheets("MTO_T").Rows.Count, "A").End(xlUp).Row
    Worksheets("MTO_T").Cells(1, 20) = LRow / 18
    Worksheets("MTO_T").Cells(1, 21).Formula = "=CEILING(RC[-1],1)"
    m = Worksheets("MTO_T").Cells(1, 21)

    'copying from MTO_ to MTO
    k = 0
    For n = 1 To m
    For i = 1 To 18
    Worksheets("MTO").Range("C" & (i + 4)) = Worksheets("MTO_T").Range("A" & (i + k))
    Worksheets("MTO").Range("I" & (i + 4)) = Worksheets("MTO_T").Range("B" & (i + k))
    Worksheets("MTO").Range("J" & (i + 4)) = Worksheets("MTO_T").Range("C" & (i + k))

    Next i
    k = (n * 18)
    Worksheets("UNIQUE").Cells(j, 9) = n
    Worksheets("UNIQUE").Cells(j, 10).Formula = "=concatenate(RC[-9],""."",RC[-1])"
    'If m <= 1 Then
    'FName = Worksheets("UNIQUE").Range("A" & j)
    'FName = Worksheets("UNIQUE").Range("A" & j)

    'Else:
    FName = Worksheets("UNIQUE").Range("J" & j)
    'End If

    Set Wb = Workbooks.Add
    ThisWorkbook.Sheets("MTO").Copy Before:=Wb.Sheets(1)
    Application.DisplayAlerts = False
    For Each Ws In Sheets
    If IsEmpty(Ws.UsedRange) Then Ws.Delete
    Next
    Application.DisplayAlerts = True



    Wb.SaveAs xPath & "\" & FName & ".xlsx"
    ActiveWorkbook.Close

    Workbooks("MTO").Worksheets("INPUT").Activate
    Next n
    Next j

    End Sub

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166
    Hello furqanglb,

    Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA HTML not running on all computers
    By Tyger0951 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2014, 06:36 AM
  2. [SOLVED] Run-time error '9': subscript out of range - error occurs on multiple computers except one
    By BrettE in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-06-2014, 11:19 PM
  3. Run-time error '2147024809' while running
    By abraham30 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-14-2013, 05:40 PM
  4. Error while running 2nd time
    By Mattn91 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-03-2012, 04:11 PM
  5. Run-time error '13' when running macro more than once
    By limp in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-13-2009, 03:51 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1