Results 1 to 8 of 8

Run Time Error 9 " Script out of range

Threaded 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

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