excel - VBA Match Function & Nested For Loops Troubleshooting -

i have 2 sheets. 1 table , contains data want entered other. other looks gantt chart, names down side , dates across top (see here).

i want program run in manner specified below run is, returns:

run-time error '438':

object doesn't support property or method

on

for each row1 in resource 

i have attempted various fixes each time adjust 1 error, seem cause another!

---

1. check table column "resource allocated" , find matching name in first column of calendar sheet.
2. check table column "date allocated" , find matching value in first row of calendar sheet.
3. select cell these intersect (the cell column number of "date allocated" , row number of "resource allocated").
4. offset column number according third table column, "time of day".
5. fill cell rgb colour specified in code.
6. repeat every row.

---

option explicit  sub calendarsync()  sheets("log").select  dim resource listcolumn dim dates listcolumn dim tod listcolumn dim row1 listrow dim row2 listrow dim row3 listrow  set resource = activesheet.listobjects("table1").listcolumns("resource allocated") set dates = activesheet.listobjects("table1").listcolumns("date allocated") set tod = activesheet.listobjects("table1").listcolumns("time of day")  dim resmatch variant dim datematch variant  each row1 in resource     'cross referencing dates & resources allocated     resmatch = application.match(resource, worksheets("calendar").columns(1), 0)     each row2 in dates         datematch = application.match(dates, worksheets("calendar").rows(1), 0)         'offsetting account time of day         each row3 in tod             if tod = "pm"                 datematch.columnoffset (1)             end if             if tod = "eve"                 datematch.columnoffset (1)             end if 'fill cell range(resmatch, datematch).interior.color = rgb(244, 66, 182)         next row3     next row2 next row1  end sub 

i've done significal changes in code. match function not work in case, think using find method gives better response. have on these changes:

option explicit  sub calendarsync()      dim resource range     dim dates range     dim tod range     dim daterow range     dim datecol range     dim lcol range     dim row1 range     dim row2 range     dim row3 range     dim range range     dim sh1 worksheet     dim sh2 worksheet      set sh1 = thisworkbook.sheets("log")     set sh2 = thisworkbook.sheets("calendar")      set resource = sh1.listobjects("table1").listcolumns("resource allocated").range     set dates = sh1.listobjects("table1").listcolumns("date allocated").range     set tod = sh1.listobjects("table1").listcolumns("time of day").range     set lcol = sh2.cells(1, sh2.cells(1, sh2.columns.count).end(xltoleft).column + 2)     set daterow = sh2.range("a1", lcol)  'set row range of dates     set datecol = sh2.range("a1", sh2.range("a" & rows.count).end(xlup)) 'set column range of resources      dim resmatch range     dim datematch range      each row1 in resource         'find resource match in column         set resmatch = datecol.find(what:=row1, lookin:=xlvalues)         if not resmatch nothing 'if has found              'find date match in row             set row2 = row1.offset(0, 1)             set datematch = daterow.find(what:=row2, lookin:=xlvalues)             if not datematch nothing 'if has found                  set row3 = row1.offset(0, 2)                  if row3 = "pm"                     set range = sh2.cells(resmatch.row, datematch.column).offset(0, 1)                 elseif row3 = "eve"                     set range = sh2.cells(resmatch.row, datematch.column).offset(0, 2)                 else                     set range = sh2.cells(resmatch.row, datematch.column)                 end if                  range.interior.color = rgb(244, 66, 182)              end if          end if     next row1 end sub 

wiki