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

Comments

Post a Comment

Popular posts from this blog

Asterisk AGI Python Script to Dialplan does not work -

-
i'm stuck project have check database of sip extension incoming caller id , dial sip extension. below receive when run script on asterisk cli. -- registered sip '9125' @ 192.168.1.102:50142 > saved useragent "zoiper rv2.8.40" peer 9125 [2017-08-22 13:53:33] notice[5095]: chan_sip.c:24558 handle_response_peerpoke: peer '9125' reachable. (17ms / 2000ms) == using sip rtp tos bits 184 == using sip rtp cos mark 5 -- executing [0112617769@from-trunk:1] answer("sip/obitrunk1-0000000d", "") in new stack > 0x1ca50d0 -- probation passed - setting rtp source address 192.168.1.98:16834 -- executing [0112617769@from-trunk:2] agi("sip/obitrunk1-0000000d", "test.py") in new stack -- launched agi script /var/lib/asterisk/agi-bin/test.py args: ['/var/lib/asterisk/agi-bin/test.py'] env line: agi_request: test.py env line: agi_channel: sip/obitrunk1-0000000d env line: agi_language: en env line: a
Read more

python - Read npy file directly from S3 StreamingBody -

-
i have many .npy (numpy) , .json files saved in s3, need download , load these files. for json files downloading files , loading dictionary using following code: obj = s3.object(bucket, key) response = obj.get() body = response['body'].read().decode('utf-8') data = json.loads(body) that way avoid writing .json file disk , reading it. now want same .npy files, equivalent? currently downloading file, saving temp file on disk , loading np.load avoid if possible. following https://stackoverflow.com/a/28196540/3509999 : obj = s3.object(bucket, key) io.bytesio(obj.get()["body"].read()) f: # rewind file f.seek(0) arr = np.load(f) wiki
Read more

kotlin - Out-projected type in generic interface prohibits the use of metod with generic parameter -

-
i start using kotlin , defined interface this: interface aadapter<vh : recyclerview.viewholder> { fun oncreateaviewholder(parent: viewgroup): vh fun onbindaviewholder(v: vh, position: int) } and when try use on code: class klasa ( private val adapter: aadapter<*> ) { fun dosth(){ //... val vh = this.adapter.oncreateaviewholder(parent) //on below line error adapter.onbindaviewholder(v, position) //... } } i error out-projected type 'aadapter<*>' prohibits use of 'public abstract fun onbindaviewholder(v: t, position: int): unit i tied add "in" or "out" definition i'm confused. how allow it. replace aadapter<*> aadapter<recyclerview.viewholder> . class klasa ( private val adapter: aadapter<recyclerview.viewholder> ) { // ... because have declared aadapter has type parameter vh type subclass of recyclerview.
Read more