excel - Find a column with specific header, then translate it into coordinates, make it a variable and include it into a function -

i need macro run on reports change (basically every time pull them, columns can in different order). headers same have made code "detects" headers , allows formulas applied on right columns.

i have problem now: need same concept (finding column header rather coordinates) applied if formula:

somewhere in spreadsheet column "degree" , somewhere else column "graduation date".
want macro find column graduation date , add new column next it, , call "b.s graduation date" (this part, got. works).

now, in "b.s graduation date" formula saying:
"if (column "degree", row 1) says "associate degree", put [(column "graduation date", row 1)]+2 years"

it simple if formula need include feature allowing find columns headers , not specific coordinates.

i thinking there might way find header, translate coordinates, translate coordinates variable, , include variable formula.

i guess like:

dim ws worksheet     dim rnglocation range     dim rngnewcol range     dim lrow long     set ws = activesheet     set rnglocation = ws.rows(1).find("degree")     'give me coordinates header   'those coordinates = (x,y)   

and select column, fill if formula variable (x,y) in there.
things need code 2 lines preceded apostrophe. also, need several variables each if function (2 sets. 1 test be, , other column values extracted: if ((x,y) = 1, (offset 0,0) = (w,z) + 2).

please let me know if can think of way or if need more information.

edit: following stucharo's indications, here code have been using:

set ws = activesheet   set rnglocation = ws.rows(1).find("graduation date")   if rnglocation nothing exit sub   rnglocation.offset(, 1).entirecolumn.insert   set rngnewcol = rnglocation.offset(, 1)   lrow = ws.cells.find("*", ws.range("a1"), searchdirection:=xlprevious).row - 1   rngnewcol.value = "b.s. graduation date"   rngnewcol.offset(1, 0).resize(lrow)   .numberformat = "general"   'the above creates new column , make in format general 'next comes stucharo's code (that tried adapt) dim degreecol integer   degreecol = ws.rows(1).find("degree").column   dim graddatecol integer   graddatecol = ws.rows(1).find("graduation date (mm/dd/yyyy)").column   dim bsgraddatecol integer   bsgraddatecol = ws.rows(1).find("b.s. graduation date").column   dim row integer   row = 2 endrow   if ws.cells(row, degreecol).value = "bachelor's degree (±16 years)"   ws.cells(row, bsgraddatecol).value = ws.cells(row, graddatecol).value   end if       if ws.cells(row, degreecol).value = "doctorate degree on (±19 years)" ws.cells(row, bsgraddatecol).value = dateadd("yyyy", -3, ws.cells(row, graddatecol).value) end if     if ws.cells(row, degreecol).value = "master's degree (±18 years)" ws.cells(row, bsgraddatecol).value = dateadd("yyyy", -2, ws.cells(row, graddatecol).value) end if     if ws.cells(row, degreecol).value = "associate's degree college diploma (±13 years)" ws.cells(row, bsgraddatecol).value = dateadd("yyyy", 3, ws.cells(row, graddatecol).value) end if     if ws.cells(row, degreecol).value = "technical diploma (±12 years)" ws.cells(row, bsgraddatecol).value = dateadd("yyyy", 4, ws.cells(row, graddatecol).value) end if     if ws.cells(row, degreecol).value = " " ws.cells(row, bsgraddatecol).value = "no data" end if next row 

this not create error not populate cells either. please if see doing wrong let me know.
thanks!!

have tried cells() property? lets reference cell row number , column number e.g. cells(1,1) (row 1, column1) same range("a1"). useful if create integer store degree column number:

dim degreecol integer degreecol = ws.rows(1).find("degree").column 

you can same thing reference "graduation date" , "b.s. graduation date":

dim graddatecol integer graddatecol = ws.rows(1).find("graduation date").column dim bsgraddatecol integer bsgraddatecol = ws.rows(1).find("b.s. graduation date").column 

now, assuming these titles on row 1 of worksheet , data starts on row 2 can reference cell using cells() property:

if ws.cells(2, degreecol).value = "associate degree"     ws.cells(2, bsgraddatecol).value = ws.cells(2, graddatecol).value + 2 end if 

on top of that, if have table can place inside for loop increment row number , work down entire table. furthermore, if have multiple operations on each row can place them inside for loop, find number of last row , step through them row wise:

dim endrow integer endrow = ws.cells(ws.rows.count, 1).end(xlup).row  dim row integer row = 2 endrow     if ws.cells(row, degreecol).value = "associate degree"         ws.cells(row, bsgraddatecol).value = dateadd("yyyy", 2, ws.cells(row, graddatecol).value)     end if      'add further if statements inside loop carry out      'additional operations on row wise basis.     'you may need create more column references using     '.find().column() method used above.  next row end if