Hi,

The code below loops through a list of values, finds each one and assigns the column location to an associated variable. The code works but is hugely inefficient, would be grateful if someone could provide something more flexible and efficient, would help going fwd, thanks in advance. 


'Creation of Array
    Data = Array("Dept", "Employee Name", "EmpNo", "Employee Name", "NI numbr", "TBASIC Amount", "REFSAL Rate", _
    "O/TIME Amount", "SMP Amount", "SAP Amount", "SPP Amount", "SSP Amount", "SSPADJ Amount", "ADJUST Amount", _
    "BAKPAY Amount", "BALOAN Amount", "BONUS Amount", "CASH Amount", "DOUBLE Amount", "EXPENS Amount", "EXPNSE Amount", _
    "GRSNET Amount", "LEAVE Amount", "LIEU-N Amount", "LIEU-S Amount", "LIEU-T Amount", "NIEXP Amount", "OTVAL Amount", _
    "PRP Amount", "RED-TX Amount", "REDUND Amount", "SHARE Amount", "SHARES Amount", "IPSHAR Amount", "TIME Amount", _
    "TME", "TAXADD Amount", "GROSS Amount", "ADVNCE Amount", "COURTN Amount", "COURTP Amount", "CRTFEE Amount", _
    "GAYE Amount", "LOAN2 Amount", "NETADV Amount", "PAID Amount", "REPAY Amount", "STLOAN Amount", "GPPEE Amount", _
    "NI Amount", "TAX Amount", "STUDNT Amount", "AEOTOT Amount", "AEOADM Amount", "NETDED Amount", "GROSS2 Amount", _
    "NIERS Amount", "GPPER Amount", "EESMEM Amount", "MPA/RC Amount", "NET2 Amount", "REFSLY AMount", "COST Amount", "Period", "Month", "LegalEnt")
    
    
    'Creation of variables
    For i = LBound(Data) To UBound(Data) Step 1
    
        Set FndRng = Cells.Find(What:=Data(i), _
                After:=ActiveCell, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False, _
                SearchFormat:=False)
        If Not FndRng Is Nothing Then
        
            Data(i) = FndRng.Column
        End If
    Next i
    
    'Assign col numbers to each variable
    deptcol = Data(0)
    empnamecol = Data(1)
    empnocol = Data(2)
    empnamecol = Data(3)
    ninumcol = Data(4)
    tbascol = Data(5)
    refcol = Data(6)
    otcol = Data(7)
    smpcol = Data(8)
    sapcol = Data(9)
    sppcol = Data(10)
    sspcol = Data(11)
    sspadjcol = Data(12)
    adjcol = Data(13)
    bakpaycol = Data(14)
    baloancol = Data(15)
    bonuscol = Data(16)
    cashcol = Data(17)
    doublecol = Data(18)
    expenscol = Data(19)
    expnsecol = Data(20)
    grsnetcol = Data(21)
    leavecol = Data(22)
    lieuncol = Data(23)
    lieuscol = Data(24)
    lieutcol = Data(25)
    niexpcol = Data(26)
    otvalcol = Data(27)
    prpcol = Data(28)
    redtxcol = Data(29)
    redundcol = Data(30)
    sharecol = Data(31)
    sharescol = Data(32)
    ipsharcol = Data(33)
    timecol = Data(34)
    tmecol = Data(35)
    taxaddcol = Data(36)
    grosscol = Data(37)
    advncecol = Data(38)
    courtncol = Data(39)
    courtpcol = Data(40)
    crtfeecol = Data(41)
    gayecol = Data(42)
    loan2col = Data(43)
    netadvcol = Data(44)
    paidcol = Data(45)
    repaycol = Data(46)
    stloancol = Data(47)
    gppeecol = Data(48)
    nicol = Data(49)
    taxcol = Data(50)
    studntcol = Data(51)
    aeototcol = Data(52)
    aeoadmcol = Data(53)
    netdedcol = Data(54)
    gross2col = Data(55)
    nierscol = Data(56)
    gppercol = Data(57)
    eesmemcol = Data(58)
    mparccol = Data(59)
    net2col = Data(60)
    refslycol = Data(61)
    costcol = Data(62)
    percol = Data(63)
    monthcol = Data(64)
    lecol = Data(65)