excel - How to insert row when finding a match in a column? -

hopefully issue described below simple one. still new vba , can't seem past current wall...good , bad days respect learning. unfortunately week has me @ loss how move on.

the macro shown below run on spreadsheet 2 sheets (mpl & cad).

• mpl sheet = simple table of information
• cad sheet contains 3 tables of varying width (i.e. first table spans column c ae, 2nd , 3rd tables span column c m). 3 tables contain project name in column c.

when macro run, starts in mpl sheet, prompts user new project name adds on new row in alphabetical order. works well.

the next step cad sheet. stated, there 3 tables. able insert new project, inserts 1 of tables new name shows in column c. @ loss. believe must find way put values of column c sort of array, count add row on each instance.

does sound logical plan? have searched endlessly way , can't seem gain ground. "irow = worksheetfunction.match(strnewproject, range("c:c")) + 1" method seems suffice on single table.

any pointers in right direction appreciated.

option explicit 'forces declaration of variables  'procedures----------------------------------------------------------------------------------- sub add_project()  '---procedure description/notes--------------------------------------------------------------- 'macro overview:     'this procedure used add new projects planner     'once macro started, user prompted new     'project name.  new name(assuming not exist)     'be added 'mpl' , 'cad' tabs.   'assumptions     'this procedure assumes list of projects contained in     'column b.  if error, update column #s below.  '---variable declarations--------------------------------------------------------------------- dim strnewproject string dim irow long  '---code-------------------------------------------------------------------------------------- 'so don't have see screen flicker code switches sheets, cells, etc. application.screenupdating = false  'go master project list sheet sheets("mpl").select  'input box prompting user project name strnewproject = inputbox("enter project name") if len(strnewproject) = 0 exit sub 'pressed cancel  'checks if project exists, displays message if true if worksheetfunction.countif(columns("b"), strnewproject) > 0    msgbox "project exists"     exit sub end if  'add new  value existing list, alphabetically irow = worksheetfunction.match(strnewproject, columns("b")) + 1 intersect(range("tmpl"), rows(irow)).insert _ ' tmpl excel table xlinsertshiftdirection.xlshiftdown, copyorigin:=excel.xlinsertformatorigin.xlformatfromleftorabove cells(irow, "b").value = strnewproject  'go cad sheet sheets("cad").select  '****this things not work way need them to***** 'add new  value existing list, alphabetically irow = worksheetfunction.match(strnewproject, range("c:c")) + 1 rows(irow).entirerow.insert cells(irow, "c").value = strnewproject  end sub 

if tables in sheet 'cad' separated blank row , tables contiguous on column c (no blanks start end individual tables) maybe work you. inserts new line first line in table, puts in project name, , sorts table project name. note tables on sheet 'cad' assumed use header row , header in column c each table "project name", adjust necessary:

sub tgr()      const strheader string = "project name"      dim wsmpl worksheet     dim wscad worksheet     dim rngfound range     dim strfirst string     dim strnewproject string      set wsmpl = sheets("mpl")     set wscad = sheets("cad")      strnewproject = inputbox("enter new project name:", "new project")     if len(strnewproject) = 0 exit sub 'pressed cancel      if worksheetfunction.countif(wsmpl.columns("b"), strnewproject) > 0         msgbox "project name [" & strnewproject & "] exists.", , "new project error"         exit sub     end if      'insert new line project name , sort data     intersect(range("tmpl"), wsmpl.rows(2)).insert     wsmpl.range("b2").value = strnewproject     range("tmpl").sort wsmpl.range("b2"), xlascending, header:=xlguess      'insert new line each table on wscad project name , sort data     wscad         set rngfound = .columns("c").find(strheader, .cells(.rows.count, "c"), xlvalues, xlwhole)         if not rngfound nothing             strfirst = rngfound.address                             rngfound.offset(1).entirerow.insert xlshiftdown                 rngfound.offset(1).value = strnewproject                 rngfound.currentregion.sort rngfound, xlascending, header:=xlyes                 set rngfound = .columns("c").find("project name", rngfound, xlvalues, xlwhole)             loop while rngfound.address <> strfirst         end if     end      set wsmpl = nothing     set wscad = nothing     set rngfound = nothing  end sub