Run Time error 13 VBA Excel
Results 1 to 2 of 2

Thread: Run Time error 13 VBA Excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2016
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Run Time error 13 VBA Excel

    I hope you are well I have some code see below. Essentially what it does is allows a user to upload two workbooks and it performs a Vlookup. The Macro was working fine until the sheets went from 65,000 to 280,000 rows. I first got the error 'run time 6 overflow' so I changed
    'Dim intLastRow to Interger' I changed it to
    'Dim intLastRow to Double' that also gave a run time error 13 to i changed it to 'Dim intLastRow to Long' and still i get run time Error 13

    The line the Error 13 is happening on is

    Code:
    .Range(.Cells(2, extraCol),.Cells(intLastRow, extraCol)).Value = Application.WorksheetFunction.VLookup(.Range(.Cells(2,1),.Cells(intLastRow,1)).Value, Consent.Sheets(1).Range("B:J"),8,False)

    I know diming
    Code:
    intLastRow As Double
    and
    Code:
    Long
    has created a mismatch but do i dim
    Code:
    intLastRow As Double
    as something else or do i amend the code else where? Any help would be greatly appreciated. the rest of my code is below.

    Code:
    Sub Add_consent()
    
        'Definition of used variables
        Dim Directory AsString'Directory for inputs and outputs
        Dim Consent_folder AsString'Directory for inputs and outputs
        Dim inputFile AsString'Input file name
        Dim currentInput AsString'Input file name
        Const DELIMITER AsString="|"'Values delimiter
        Dim OutputFile AsString'Output file name
        Dim lngCount AsLong'selected files count
        Dim wbkOutput As Workbook 'output workbook
        Dim wbkTemp As Workbook 'temporary workbook
        Dim myWkBook As Workbook 'Input Workbook
        Dim Consent As Workbook 'Consent file
        Dim Consent_name 'new opened file
        Dim myWkSheet As Worksheet 'Input Worksheet
        Dim sheetNum AsLong'Variable for sheet number
        Dim sheetNames()AsString'output worksheet sheet names
        Dim sheetInterfaceName 'Sheet name representing DID interface
        Dim Active As Worksheet 'Active worksheet
        Dim intLastRow AsDouble'Last Row Element
        Dim Error_Codes As Worksheet ' Sheet containing error codes
        Dim myRecord As Range 'Record for output
        Dim myField As Range 'Cell value for output
        Dim nFileNum AsLong'Variable for file number
        Dim sOut AsString'Text to be written into file
        Dim invalidDelete AsString'Case of invalid delete attempt
        Dim sheetIndex AsLong' Current sheet index
        Dim Selected AsLong'
        Dim rwCount AsLong'Number of current sheet rows containing data in tracking file
        Dim colCount AsInteger'Number of current sheet columns containing data in tracking file
        Dim extraCol AsInteger'Number of current sheet columns containing data in tracking file
        Dim indexRow AsLong'Row index
        Dim helpRow AsLong'
        Dim AddIn AsInteger
        Dim selectedCount AsInteger
        Dim int1 AsLong
        Dim int2 AsInteger
        Dim int3 AsInteger
    
        'General application settings
        Application.ScreenUpdating =False'Turns off switching to exported excel file once it gets opened
        Application.DisplayAlerts =False'Turns off automatic alert messages
        Application.EnableEvents =False'
        Application.AskToUpdateLinks =False'Turns off the "update links" prompt
    
        'User prompt, choose HCP file
        MsgBox "Choose HCP/HCO file missing consent information"
    
        'Alternative way to open the file
        Dim fd As FileDialog
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        fd.AllowMultiSelect =False
    
        'Assign a number for the selected file
        Dim FileChosen AsInteger
        FileChosen = fd.Show
        If FileChosen <>-1Then
        'Didn't choose anything (clicked on CANCEL)
            MsgBox "No file selected - aborted"
            End'Ends file fetch and whole sub
        EndIf
    
        Dim fss AsObject
        Set fss = CreateObject("Scripting.FilesystemObject")
        inputFile = Dir(fd.SelectedItems(1))'parses only the name of file
        Directory = fss.getParentFolderName(fd.SelectedItems(1))&"\"'parses only directory of the file
    
        'Open HCP file .xlsx spreadsheet
        Set wbkTemp = Workbooks.Open(Filename:=Directory & inputFile)
        'Set wbkTemp = Workbooks(Workbooks.Count)
    
        'Get number of columns in the HCP file
        colCount = wbkTemp.Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
    
        'Get the number of rows in the HCP file
        intLastRow = wbkTemp.Sheets(1).Cells(Rows.Count,1).End(xlUp).Row
    
        'Set GCM_ID format to number
        wbkTemp.Sheets(1).Range(wbkTemp.Sheets(1).Cells(2,1), wbkTemp.Sheets(1).Cells(intLastRow,1)).Select'Specify the range which suits your purpose
        With Selection
            Selection.NumberFormat ="General"
            .Value =.Value
        EndWith
    
        'Prompt user for the second file
        MsgBox "Select file(s) containing Consent information"
    
        'Open Consent file dialog
        Dim filedial As FileDialog
        Set filedial = Application.FileDialog(msoFileDialogOpen)
    
        Dim chosen AsInteger
        chosen = filedial.Show
        If chosen <>-1Then
        'Didn't choose anything (clicked on CANCEL)
            MsgBox "No file selected - aborted"
            End'Ends file fetch and whole sub
        EndIf
    
        'Number of selected files
        selectedCount = filedial.SelectedItems.Count
    
        'Extra variable
        AddIn =0
    
        For Selected =1To selectedCount
        'Open file with Consent info
        Consent_name = Dir(filedial.SelectedItems(Selected))
        'Consent_folder
        Workbooks.OpenText Filename:=Consent_name, StartRow:=1, DataType:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|"
        Set Consent = Workbooks(Workbooks.Count)
    
        'Number of rows in consent file
        rwCount = Consent.Sheets(1).Cells(Rows.Count,1).End(xlUp).Row
    
        'Specify the column to paste data
        extraCol = colCount + AddIn +1
    
        '1)
        'VLOOKUP across spreadsheets for consent data
        'wbkTemp.Sheets(1).Cells(1, 1).Copy
        'wbkTemp.Sheets(1).Cells(1, extraCol).PasteSpecial Paste:=xlPasteFormats
        'wbkTemp.Sheets(1).Cells(1, extraCol).Value = "Consent"
        'With wbkTemp.Sheets(1)
            '.Range(.Cells(2, extraCol), .Cells(intLastRow, extraCol)).Value = Application.WorksheetFunction.VLookup(.Range(.Cells(2, 1), .Cells(intLastRow, 1)).Value, Consent.Sheets(1).Range("B:J"), 8, False)
        'End With
    
        '2)
        'VLOOKUP across spreadsheets for consent data
        'wbkTemp.Sheets(1).Cells(1, 1).Copy
        'wbkTemp.Sheets(1).Cells(1, extraCol).PasteSpecial Paste:=xlPasteFormats
        'wbkTemp.Sheets(1).Cells(1, extraCol).Value = "Consent"
        'With wbkTemp.Sheets(1)
        '    '.Range(.Cells(2, extraCol), .Cells(intLastRow, extraCol)).Value = Application.WorksheetFunction.VLookup(.Range(.Cells(2, 1), .Cells(intLastRow, 1)).Value, Consent.Sheets(1).Range("B:J"), 8, False)
        '     For int1 = 2 To intLastRow
        '        if Application.WorksheetFunction.IsNA(Application.WorksheetFunction.VLookup(.Cells()))
        '
        '     Next int1
        'End With
    
        '3)
        'VLOOKUP across spreadsheets for consent data
        wbkTemp.Sheets(1).Cells(1,1).Copy
        wbkTemp.Sheets(1).Cells(1, extraCol).PasteSpecial Paste:=xlPasteFormats
        wbkTemp.Sheets(1).Cells(1, extraCol).Value ="Consent"
        With wbkTemp.Sheets(1)
            .Range(.Cells(2, extraCol),.Cells(intLastRow, extraCol)).Value = Application.WorksheetFunction.VLookup(.Range(.Cells(2,1),.Cells(intLastRow,1)).Value, Consent.Sheets(1).Range("B:J"),8,False)
        EndWith
    
        'Close the file with consent information
        Consent.Close
    
        'Loop again for next file
        AddIn = AddIn +1
        Next Selected
    
        'Deal with N/A values
        With wbkTemp.Sheets(1)
            For int1 =2To intLastRow
                For int2 =1To selectedCount
                    IfNot Application.WorksheetFunction.IsNA(.Cells(int1, colCount + int2).Value)Then
                        .Cells(int1, colCount +1).Value =.Cells(int1, colCount + int2).Value
                    EndIf
                Next int2
            Next int1
        EndWith
    
        'Remove extra columns
        With wbkTemp.Sheets(1)
            .Columns(fnColumnToLetter_Split(colCount +2)&":"& fnColumnToLetter_Split(extraCol + selectedCount)).Delete Shift:=xlToLeft
        EndWith
    
        'Save and close the new workbook
        With wbkTemp
            'Save and close the new workbook
            .SaveAs Filename:=inputFile
            .Close True
        EndWith
    
        MsgBox "Available consent information added"
    
    
    EndSub
    
    Function fnColumnToLetter_Split(ByVal intColumnNumber AsInteger)
        fnColumnToLetter_Split = Split(Cells(1, intColumnNumber).Address,"$")(1)
    EndFunction
    Last edited by Conell8383; Aug 12th, 2016 at 06:20 AM.

  2. #2
    Board Regular
    Join Date
    Jul 2016
    Posts
    66
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run Time error 13 VBA Excel

    Hi All

    here is how you fix it

    you set
    Code:
    Dim intLastRow As Interger
    to
    Code:
    Dim intLastRow As Long
    and you change
    Code:
    .Range(.Cells(2, extraCol),.Cells(intLastRow, extraCol)).Value = Application.WorksheetFunction.VLookup(.Range(.Cells(2,1),.Cells(intLastRow,1)).Value, Consent.Sheets(1).Range("B:J"),8,False)
    to
    Code:
    .Range(.Cells(2, extraCol), .Cells(intLastRow, extraCol)).Value = Application.WorksheetFunction.VLookup(.Range(.Cells(2, 1), .Cells(intLastRow, 1)), Consent.Sheets(1).Range("B:J"), 8, False)
    I hope this helps someone out
    Last edited by Conell8383; Aug 12th, 2016 at 07:18 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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