Posts by KjBox

    Try this alternative approach, file attached


    Code assigned to the button

    Files

    CurrentRegion stops at an empty row, so the "Record Only" would not be included. Try

    Not surprised you were confused by this question, Rory!


    Hanes was trying to adapt a bit of VBA I had written for him to suit a couple of changes in the VLOOKUP tables.


    The solution he was looking for was:

    Code
    1. For i = 2 To .Cells(.Rows.Count, 7).End(xlUp).Row
    2. .Cells(i, lCol) = _
    3. Application.VLookup(Application.VLookup(.Cells(i, 6), r2, 2, 0), r, _
    4. Application.Match(Application.VLookup(.Cells(i, 8), r3, 2, 0), r.Rows(1), 0), 0)
    5. Next

    where r, r2 & r3 are Range variables set to 3 different tables in 3 different workbooks. The results are placed in a 4th workbook.

    Like this?

    Try this

    It is very difficult when your sample files bare no resemblance to your actual file, and you are unable to adapt code that works on your sample file to suit your actual file.


    You really need to attach your actual file and explain exactly what it is you need, then those trying to help you will not waste time.


    All I could go on was the code you had, which copied Column AH of the Source sheet to Column A of the Destination sheet, I thought you wanted to change that to column D.


    Now you want to use Columns I and J in the source file, is that correct?

    Thanks for the files you sent.


    First you need to change the Input file a bit, replace "Dom Rep" with "Dominican Republic" and replace "Trinidad & Tobago" with "Trinidad and Tobago"


    Then change your code to this

    Try this

    The code loads the data from Columns AH & AI of the Source Sheet into array x. Code then loops through array x and builds array y if the second dimension of x is not blank. The code then places the contents of array y onto the Destination sheet.

    Try this, the code needs to be placed in the Worksheet Object Module for Sheet4

    When sorting "00" is treated the same as "0". Assuming you are using whole numbers only then a work-around is to first change the "0"s to "0.1"s, then sort, then change the "0.1"s back to "0"s.


    Try replacing the code you have with this