Run Time error 13 VBA Excel

C

Conell8383

Board Regular
Joined
Jul 26, 2016
Messages
66
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: 
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336](.[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] extraCol[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]intLastRow[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] extraCol[/COLOR][COLOR=#303336])).[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]WorksheetFunction[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]VLookup[/COLOR][COLOR=#303336](.[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336](.[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]intLastRow[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])).[/COLOR][COLOR=#303336]Value[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] Consent[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"B:J"[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#7D2727]8[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336])[/COLOR]</code>

I know diming
Code: 
<code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: pre-wrap; color: rgb(36, 39, 41); background-color: rgb(239, 240, 241);">intLastRow As Double</code>
and
Code: 
[COLOR=#242729][FONT=Consolas]Long[/FONT][/COLOR]
has created a mismatch but do i dim
Code: 
<code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: pre-wrap; color: rgb(36, 39, 41); background-color: rgb(239, 240, 241);">intLastRow As Double</code>
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: 
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#101094]Sub[/COLOR][COLOR=#303336] Add_consent[/COLOR][COLOR=#303336]()[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'Definition of used variables[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] Directory [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]String[/COLOR][COLOR=#858C93]'Directory for inputs and outputs[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] Consent_folder [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]String[/COLOR][COLOR=#858C93]'Directory for inputs and outputs[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] inputFile [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]String[/COLOR][COLOR=#858C93]'Input file name[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] currentInput [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]String[/COLOR][COLOR=#858C93]'Input file name[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Const[/COLOR][COLOR=#303336] DELIMITER [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]"|"[/COLOR][COLOR=#858C93]'Values delimiter[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] OutputFile [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]String[/COLOR][COLOR=#858C93]'Output file name[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] lngCount [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#858C93]'selected files count[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] wbkOutput [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Workbook [/COLOR][COLOR=#858C93]'output workbook[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] wbkTemp [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Workbook [/COLOR][COLOR=#858C93]'temporary workbook[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] myWkBook [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Workbook [/COLOR][COLOR=#858C93]'Input Workbook[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] Consent [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Workbook [/COLOR][COLOR=#858C93]'Consent file[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] Consent_name [/COLOR][COLOR=#858C93]'new opened file[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] myWkSheet [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Worksheet [/COLOR][COLOR=#858C93]'Input Worksheet[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] sheetNum [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#858C93]'Variable for sheet number[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] sheetNames[/COLOR][COLOR=#303336]()[/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]String[/COLOR][COLOR=#858C93]'output worksheet sheet names[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] sheetInterfaceName [/COLOR][COLOR=#858C93]'Sheet name representing DID interface[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] Active [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Worksheet [/COLOR][COLOR=#858C93]'Active worksheet[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] intLastRow [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Double[/COLOR][COLOR=#858C93]'Last Row Element[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] Error_Codes [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Worksheet [/COLOR][COLOR=#858C93]' Sheet containing error codes[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] myRecord [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Range [/COLOR][COLOR=#858C93]'Record for output[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] myField [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] Range [/COLOR][COLOR=#858C93]'Cell value for output[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] nFileNum [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#858C93]'Variable for file number[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] sOut [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]String[/COLOR][COLOR=#858C93]'Text to be written into file[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] invalidDelete [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]String[/COLOR][COLOR=#858C93]'Case of invalid delete attempt[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] sheetIndex [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#858C93]' Current sheet index[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] Selected [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#858C93]'[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] rwCount [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#858C93]'Number of current sheet rows containing data in tracking file[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] colCount [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Integer[/COLOR][COLOR=#858C93]'Number of current sheet columns containing data in tracking file[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] extraCol [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Integer[/COLOR][COLOR=#858C93]'Number of current sheet columns containing data in tracking file[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] indexRow [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#858C93]'Row index[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] helpRow [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#858C93]'[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] AddIn [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Integer[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] selectedCount [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Integer[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] int1 [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] int2 [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Integer[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] int3 [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Integer[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'General application settings[/COLOR][COLOR=#303336]
    Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ScreenUpdating [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#858C93]'Turns off switching to exported excel file once it gets opened[/COLOR][COLOR=#303336]
    Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]DisplayAlerts [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#858C93]'Turns off automatic alert messages[/COLOR][COLOR=#303336]
    Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]EnableEvents [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#858C93]'[/COLOR][COLOR=#303336]
    Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]AskToUpdateLinks [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#858C93]'Turns off the "update links" prompt[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'User prompt, choose HCP file[/COLOR][COLOR=#303336]
    MsgBox [/COLOR][COLOR=#7D2727]"Choose HCP/HCO file missing consent information"[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'Alternative way to open the file[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] fd [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] FileDialog
    [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] fd [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]FileDialog[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]msoFileDialogFilePicker[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    fd[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]AllowMultiSelect [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'Assign a number for the selected file[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] FileChosen [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Integer[/COLOR][COLOR=#303336]
    FileChosen [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] fd[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Show
    [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] FileChosen [/COLOR][COLOR=#303336]<>[/COLOR][COLOR=#303336]-[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'Didn't choose anything (clicked on CANCEL)[/COLOR][COLOR=#303336]
        MsgBox [/COLOR][COLOR=#7D2727]"No file selected - aborted"[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#858C93]'Ends file fetch and whole sub[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] fss [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Object[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] fss [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] CreateObject[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"Scripting.FilesystemObject"[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    inputFile [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Dir[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]fd[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]SelectedItems[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]))[/COLOR][COLOR=#858C93]'parses only the name of file[/COLOR][COLOR=#303336]
    Directory [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] fss[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]getParentFolderName[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]fd[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]SelectedItems[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]))[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#7D2727]"\"[/COLOR][COLOR=#858C93]'parses only directory of the file[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'Open HCP file .xlsx spreadsheet[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] wbkTemp [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Workbooks[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Open[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Filename[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]Directory [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] inputFile[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'Set wbkTemp = Workbooks(Workbooks.Count)[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'Get number of columns in the HCP file[/COLOR][COLOR=#303336]
    colCount [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] wbkTemp[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] Columns[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Count[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlToLeft[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Column

    [/COLOR][COLOR=#858C93]'Get the number of rows in the HCP file[/COLOR][COLOR=#303336]
    intLastRow [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] wbkTemp[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Rows[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Count[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlUp[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Row

    [/COLOR][COLOR=#858C93]'Set GCM_ID format to number[/COLOR][COLOR=#303336]
    wbkTemp[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]wbkTemp[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336] wbkTemp[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]intLastRow[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])).[/COLOR][COLOR=#101094]Select[/COLOR][COLOR=#858C93]'Specify the range which suits your purpose[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] Selection
        Selection[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]NumberFormat [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]"General"[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Value
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'Prompt user for the second file[/COLOR][COLOR=#303336]
    MsgBox [/COLOR][COLOR=#7D2727]"Select file(s) containing Consent information"[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'Open Consent file dialog[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] filedial [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#303336] FileDialog
    [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] filedial [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]FileDialog[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]msoFileDialogOpen[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] chosen [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Integer[/COLOR][COLOR=#303336]
    chosen [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] filedial[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Show
    [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336] chosen [/COLOR][COLOR=#303336]<>[/COLOR][COLOR=#303336]-[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'Didn't choose anything (clicked on CANCEL)[/COLOR][COLOR=#303336]
        MsgBox [/COLOR][COLOR=#7D2727]"No file selected - aborted"[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#858C93]'Ends file fetch and whole sub[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'Number of selected files[/COLOR][COLOR=#303336]
    selectedCount [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] filedial[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]SelectedItems[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Count

    [/COLOR][COLOR=#858C93]'Extra variable[/COLOR][COLOR=#303336]
    AddIn [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] Selected [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#101094]To[/COLOR][COLOR=#303336] selectedCount
    [/COLOR][COLOR=#858C93]'Open file with Consent info[/COLOR][COLOR=#303336]
    Consent_name [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Dir[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]filedial[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]SelectedItems[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Selected[/COLOR][COLOR=#303336]))[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'Consent_folder[/COLOR][COLOR=#303336]
    Workbooks[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]OpenText Filename[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]Consent_name[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] StartRow[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] DataType[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]xlDelimited[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] ConsecutiveDelimiter[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] Tab[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] Semicolon[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] Comma[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] Space[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] Other[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#7D2727]True[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] OtherChar[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#7D2727]"|"[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Set[/COLOR][COLOR=#303336] Consent [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Workbooks[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Workbooks[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Count[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'Number of rows in consent file[/COLOR][COLOR=#303336]
    rwCount [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Consent[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Rows[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Count[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlUp[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Row

    [/COLOR][COLOR=#858C93]'Specify the column to paste data[/COLOR][COLOR=#303336]
    extraCol [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] colCount [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#303336] AddIn [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'1)[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'VLOOKUP across spreadsheets for consent data[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'wbkTemp.Sheets(1).Cells(1, 1).Copy[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'wbkTemp.Sheets(1).Cells(1, extraCol).PasteSpecial Paste:=xlPasteFormats[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'wbkTemp.Sheets(1).Cells(1, extraCol).Value = "Consent"[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'With wbkTemp.Sheets(1)[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#858C93]'.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)[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'End With[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'2)[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'VLOOKUP across spreadsheets for consent data[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'wbkTemp.Sheets(1).Cells(1, 1).Copy[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'wbkTemp.Sheets(1).Cells(1, extraCol).PasteSpecial Paste:=xlPasteFormats[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'wbkTemp.Sheets(1).Cells(1, extraCol).Value = "Consent"[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'With wbkTemp.Sheets(1)[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'    '.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)[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'     For int1 = 2 To intLastRow[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'        if Application.WorksheetFunction.IsNA(Application.WorksheetFunction.VLookup(.Cells()))[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'     Next int1[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'End With[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'3)[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#858C93]'VLOOKUP across spreadsheets for consent data[/COLOR][COLOR=#303336]
    wbkTemp[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Copy
    wbkTemp[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] extraCol[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]PasteSpecial Paste[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]xlPasteFormats
    wbkTemp[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] extraCol[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]"Consent"[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] wbkTemp[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336](.[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] extraCol[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]intLastRow[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] extraCol[/COLOR][COLOR=#303336])).[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]WorksheetFunction[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]VLookup[/COLOR][COLOR=#303336](.[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336](.[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]intLastRow[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])).[/COLOR][COLOR=#303336]Value[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] Consent[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"B:J"[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#7D2727]8[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'Close the file with consent information[/COLOR][COLOR=#303336]
    Consent[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Close

    [/COLOR][COLOR=#858C93]'Loop again for next file[/COLOR][COLOR=#303336]
    AddIn [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] AddIn [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336] Selected

    [/COLOR][COLOR=#858C93]'Deal with N/A values[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] wbkTemp[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] int1 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#101094]To[/COLOR][COLOR=#303336] intLastRow
            [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] int2 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#101094]To[/COLOR][COLOR=#303336] selectedCount
                [/COLOR][COLOR=#101094]If[/COLOR][COLOR=#101094]Not[/COLOR][COLOR=#303336] Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]WorksheetFunction[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]IsNA[/COLOR][COLOR=#303336](.[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]int1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] colCount [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#303336] int2[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value[/COLOR][COLOR=#303336])[/COLOR][COLOR=#101094]Then[/COLOR][COLOR=#303336]
                    [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]int1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] colCount [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]int1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] colCount [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#303336] int2[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Value
                [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]If[/COLOR][COLOR=#303336]
            [/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336] int2
        [/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336] int1
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'Remove extra columns[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] wbkTemp[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Columns[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]fnColumnToLetter_Split[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]colCount [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#7D2727]":"[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] fnColumnToLetter_Split[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]extraCol [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#303336] selectedCount[/COLOR][COLOR=#303336])).[/COLOR][COLOR=#303336]Delete Shift[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]xlToLeft
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#858C93]'Save and close the new workbook[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336] wbkTemp
        [/COLOR][COLOR=#858C93]'Save and close the new workbook[/COLOR][COLOR=#303336]
        [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]SaveAs Filename[/COLOR][COLOR=#303336]:=[/COLOR][COLOR=#303336]inputFile
        [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Close [/COLOR][COLOR=#7D2727]True[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]With[/COLOR][COLOR=#303336]

    MsgBox [/COLOR][COLOR=#7D2727]"Available consent information added"[/COLOR][COLOR=#303336]


[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]Sub[/COLOR][COLOR=#303336]

[/COLOR][COLOR=#101094]Function[/COLOR][COLOR=#303336] fnColumnToLetter_Split[/COLOR][COLOR=#303336]([/COLOR][COLOR=#101094]ByVal[/COLOR][COLOR=#303336] intColumnNumber [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Integer[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    fnColumnToLetter_Split [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Split[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] intColumnNumber[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Address[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]"$"[/COLOR][COLOR=#303336])([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]Function[/COLOR]</code>
 
Last edited:

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
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: 
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; border: 0px; white-space: inherit;">[COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336](.[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] extraCol[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]intLastRow[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] extraCol[/COLOR][COLOR=#303336])).[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Application[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]WorksheetFunction[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]VLookup[/COLOR][COLOR=#303336](.[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336](.[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]2[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Cells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]intLastRow[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])).[/COLOR][COLOR=#303336]Value[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] Consent[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Sheets[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"B:J"[/COLOR][COLOR=#303336]),[/COLOR][COLOR=#7D2727]8[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]False[/COLOR][COLOR=#303336])[/COLOR]</code>
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:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Macro crashing and closing excel
Replies
1
Views
201
Beneindias
B
S
  • Question
"Subscript out of range ( Run time Error: 09)" Problem in My Excel VBA
Replies
5
Views
245
6StringJazzer
6StringJazzer
B
  • Question
Automatically run this VBA macro everyday at a given time
Replies
2
Views
1K
Logit
Logit
Anigito
  • Solved
Macro stops in the middle of cycle + Macros are not selectable for assigning [Run-time error -21473219767 (80028029)]
Replies
2
Views
745
Anigito
Anigito
P
  • Solved
VBA to send multiple emails, run time error 440 after ~30 emails
Replies
5
Views
822
Domenic
D

Forum statistics

Threads
1,214,589
Messages
6,120,416
Members
448,960
Latest member
AKSMITH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top