excel vba - XLS VBA VlookUp using RC from static to dynamic -

i trying create macro vlook-up.

i have 2 sheets. managed make work vlookup hardcoded.

can make dynamic?

here code :

    option explicit      sub vlookup4()      dim nrcolsold, nrcolsnew integer 'numarul de celule in primul rand, incepand cu a1 sheet "old" si "new"     dim foundold, foundnew range     dim lrold, lrnew long     dim cauta variant     'cauta = inputbox("filtru dupa ce coloana?")      sheets("old").select     activesheet         nrcolsold = .cells(1, .columns.count).end(xltoleft).column ' calculeaza care e ultima coloana din sheet     end     'set found = rows(1).find(what:=cauta, lookin:=xlvalues, lookat:=xlwhole)     set foundold = rows(1).find(what:="numar", lookin:=xlvalues, lookat:=xlwhole)     if foundold nothing exit sub     lrold = cells(rows.count, foundold.column).end(xlup).row     activesheet.range(cells(1, 1), cells(lrold, foundold.column + nrcolsold)).autofilter     worksheets("old").range(cells(1, 1), cells(lrold, foundold.column + nrcolsold)).columns.autofit     range(cells(1, 1), cells(lrold, nrcolsold)).select 'selecteaza celulele ce contin valori     activeworkbook.worksheets("old").autofilter.sort.sortfields.add key:=range(cells(1, foundold.column), cells(1, foundold.column)), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal     'range(cells(1, found.column), cells(1, found.column)).select   'selecteaza doar celula cu numele celulei dupa care facem ordonarea     activeworkbook.worksheets("old").autofilter.sort         .header = xlyes         .matchcase = false         .orientation = xltoptobottom         .sortmethod = xlpinyin         .apply     end       sheets("new").select     activesheet         nrcolsnew = .cells(1, .columns.count).end(xltoleft).column ' calculeaza care e ultima coloana din sheet     end     'set found = rows(1).find(what:=cauta, lookin:=xlvalues, lookat:=xlwhole)     set foundnew = rows(1).find(what:="numar", lookin:=xlvalues, lookat:=xlwhole)     if foundnew nothing exit sub     lrnew = cells(rows.count, foundnew.column).end(xlup).row     activesheet.range(cells(1, 1), cells(lrnew, foundnew.column + nrcolsnew)).autofilter     worksheets("new").range(cells(1, 1), cells(lrnew, foundnew.column + nrcolsnew)).columns.autofit     range(cells(1, 1), cells(lrnew, nrcolsnew + 1)).select 'selecteaza celulele ce contin valori + 1 se adauga pentru ca se insereaza o coloana     activeworkbook.worksheets("new").autofilter.sort.sortfields.add key:=range(cells(1, foundnew.column), cells(1, foundnew.column)), sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal     'range(cells(1, found.column), cells(1, found.column)).select   'selecteaza doar celula cu numele celulei dupa care facem ordonarea     activeworkbook.worksheets("new").autofilter.sort         .header = xlyes         .matchcase = false         .orientation = xltoptobottom         .sortmethod = xlpinyin         .apply     end     foundnew.offset(, 1).entirecolumn.insert     cells(1, foundnew.column + 1).value = "new vs old"      dim unu, doi, trei integer     unu = 1     doi = 2     trei = 3      range(cells(2, foundnew.column + 1), cells(lrnew, foundnew.column + 1)).formular1c1 = "=vlookup(rc[-1],old!c[-3]:c[-2],2,0)"     'range(cells(2, foundnew.column + 1), cells(lrnew, foundnew.column + 1)).formular1c1 = "=vlookup(rc[-unu],old!c[-trei]:c[-doi],2,0)" '    range(cells(2, foundnew.column + 1), cells(lrnew, foundnew.column + 1)).select '    selection.copy '    selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=false '    selection.replace what:="#n/a", replacement:="intrari noi", lookat:=xlpart, searchorder:=xlbyrows, matchcase:=false, searchformat:=false, replaceformat:=false '    activesheet.range(cells(2, foundnew.column + 1), cells(lrnew, foundnew.column + 1)).autofilter field:=5, criteria1:="intrari noi" end sub 

first want transform

range(cells(2, foundnew.column + 1), cells(lrnew, foundnew.column + 1)).formular1c1 = "=vlookup(rc[-1],old!c[-3]:c[-2],2,0)" 

into

dim unu, doi, trei integer     unu = 1     doi = 2     trei = 3      range(cells(2, foundnew.column + 1), cells(lrnew, foundnew.column + 1)).formular1c1 = "=vlookup(rc[-unu],old!c[-trei]:c[-doi],2,0)" 

can give me hint please.

thanks.

change statement to:

dim unu, doi, trei integer     unu = 1     doi = 2     trei = 3      range(cells(2, foundnew.column + 1), cells(lrnew, foundnew.column + 1)).formular1c1 = "=vlookup(rc[-" & unu & "],old!c[-" & trei & "]:c[-" & doi & "],2,0)" 

explanation: if attempted use code presented it, vba interpret unu, doi, , trei literal strings represent, , end =vlookup(rc[-unu],old!c[-trei]:c[-dui],2,0) in cell, excel won't know with.

when concatenate them vlookup string via &, vba able pass values of variables unu (1), doi (2), , trei (3) vlookup string. =vlookup(rc[-1],old!c[-3]:c[-2],2,0) in cell instead, , excel can work that.