+ Reply to Thread
Results 1 to 8 of 8

Run Time Error 9 " Script out of range

Hybrid View

  1. #1
    Registered User
    Join Date
    12-17-2011
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    81

    Run Time Error 9 " Script out of range

    Dear Friends

    I write below formula, but no luck getting Run time error 9, attached workbook for further information, getting error on underlined area

    Private Sub CommandButton1_Click()
      Dim rng As Range
      Dim temp As Variant
      Dim I As Long
      Dim a As Long
      Dim rng_dest As Range
      Dim LastRow As String
      Dim wkb As Workbook
      Dim wks As Worksheet
        
      Application.ScreenUpdating = False
    '*******************************************************************************************************
      Set wkb = Workbooks.Open("D:\Purchase Order Data\Purchase Order Data.xlsb", Password:="vv2325", WriteResPassword:="vv2325")
      Set wks = wkb.Sheets("PO Data")
      LastRow = wks.Range("E1048576").End(xlUp).Row + 1
      
      I = 1
      
      Set rng_dest = wks.Range("E:O")
      
      ' Find first empty row in columns D:J on sheet Master Data
      
      Do Until WorksheetFunction.CountA(rng_dest.Rows(I)) = 0
      
        I = I + 1
        
      Loop
      
      'Copy range B16:I34 on sheet Purchase Order to Variant array
      
      Set rng = Sheets("Purchase Order").Range("B15:L34")
      
    
      
      For a = 1 To rng.Rows.Count
      
        If WorksheetFunction.CountA(rng.Rows(a)) <> 1 Then
        
          rng_dest.Rows(I).Value = rng.Rows(a).Value
          
          'Copy Purchase Order number
      
          wks.Range("A" & LastRow).Value = Sheets("Purchase Order").Range("I3").Value
            
          'Copy PO Number2
          
          'wks.Range("M" & LastRow).Value = Sheets("Purchase Order").Range("J3").Value
            
            
          'Copy Date
          
          wks.Range("B" & LastRow).Value = Sheets("Purchase Order").Range("I4").Value
          
          'Copy Company name
          
          wks.Range("C" & LastRow).Value = Sheets("Purchase Order").Range("C8").Value
          
           'Copy PO Value
          
          wks.Range("D" & LastRow).Value = Sheets("Purchase Order").Range("L41").Value
          
          
          I = I + 1
            ActiveWorkbook.RefreshAll
        End If
        
      Next a
              
      Application.ScreenUpdating = True
    End Sub
    Thanks in advance
    Vaibhav
    Attached Files Attached Files

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Run Time Error 9 " Script out of range

    there isn't a sheet called Purchase Order in the purchase order data workbook
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    12-17-2011
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: Run Time Error 9 " Script out of range

    Sir,

    i have already set
       Set wks = wkb.Sheets("PO Data")
    in purchase order data workbook

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Run Time Error 9 " Script out of range

    The error is not on this line

      Set rng = Sheets("Purchase Order").Range("B15:L34")
    More likely on these two lines

    Set wkb = Workbooks.Open("D:\Purchase Order Data\Purchase Order Data.xlsb", Password:="vv2325", WriteResPassword:="vv2325")
      Set wks = wkb.Sheets("PO Data")

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Run Time Error 9 " Script out of range

    your subscript error occurs here
    Set rng = Sheets("Purchase Order").Range("B15:L34")
    because there is no sheet called Purchase Order

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: Run Time Error 9 " Script out of range

    As JosephP said the workbook you open does not contain the sheet you are trying to reference.
    Because you have not qualified the Sheets reference you will default to the activeworkbook.

    You can use Thisworkbook reference to resolve that, you will need to use it in all lines of code, or create an object to reference the sheet as you have done for "PO Data".

    Set rng = ThisWorkbook.Sheets("Purchase Order").Range("B15:L34")
    Cheers
    Andy
    www.andypope.info

  7. #7
    Registered User
    Join Date
    12-17-2011
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: Run Time Error 9 " Script out of range

    Sir,

    i want to copy
     Set rng = Sheets("Purchase Order").Range("B15:L34")
    above range in to the following range
     Set rng_dest = wks.Range("E:O")

  8. #8
    Registered User
    Join Date
    12-17-2011
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: Run Time Error 9 " Script out of range

    @ Andy Pope

    Working Perfect.

    Thank you so much

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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