excel - How to avoid using select in VBA for variable cell ranges? -

i have heard of dislike using .select in vba excel macros, wondering how particular goal can achieved without use? example, there cell(used header) value "commodity". beneath it, cells need have vlookup function. however, on each , every iteration of macro, column shift (as new columns added) , new rows added (so newly added rows need have function added well). how possible consistently locate commodity column , find lowest unfilled row? simple using select:

do until activecell.value = "commodity" activecell.offset(0,1).select loop until activecell.value = "" activecell.offset(1,0).select loop 

obviously, prefer avoid using type of syntax, not know how around it. answers have seen regarding avoidance of select appear set, example, rng = cell(x,y) or something, known-location cells. not know how without utilizing select check cell values.

first find column sting located, count rows beside it, set range , enter formula.

sub findcolumn()     dim f range, c integer     dim lstrw long, rng range      set f = rows(1).find(what:="commodity", lookat:=xlwhole)      if not f nothing         c = f.column     else: msgbox "not found"           exit sub      end if      lstrw = cells(rows.count, c - 1).end(xlup).row     set rng = range(cells(2, c), cells(lstrw, c))     rng = "my formula"  end sub