Error "object required" in VBA , referred to duplicate questions -

my purpose split task constituent tasks , find important one.the macro written in "may" sheet of workallotment.xlsm , tasks in tasks.xlsx

for example:

                                   constituents  constituents  important imp praveen t1  t2  t3  t4  t5  t6     t1+t2+t3 =t5  t3+t5+t6 =t9     t1      t6          4  3   1   2   8   9                karthik p1  p2  p3  p4          " among t1,t2,t3- t1 takes more time".its imp                 6  3   2   2                        walter  c1  c2  c3  c4                               1   2   3   4                        arvind  g1  g2  g3                                    2  1   3                            sreelatha   h1  h2  h3                                       2   1   1                            

code:

sub workallotment()     dim workallotmentwb, taskswb workbook     dim washeet worksheet     dim str(9) string     dim splitarray() string, s(10) string     dim col_new integer      dim wa_namerng range      dim r integer, max integer, imps string     dim wa_namerow, wa_firstrow, wa_lastrow integer  'work allotment rows     dim t_firstrow, t_lastrow integer                'task rows      dim curtaskcol integer   'current task column     dim wa_tmpcol integer    'work allotment, temp column           set workallotmentwb = thisworkbook         set taskswb = workbooks.open("e:/tasks.xlsx")          'notes on data structure:         '- tasks workbook:             'first name starts in a1 of "sheet1"         '- workallotment workbook:             'first name starts in a2 of sheet named "workallotment"             'tasks written starting in b2             'in row 1 headers (number of days)          t_firstrow = 1         wa_firstrow = 2         wa_namerow = 0          set washeet = workallotmentwb.worksheets("may")             ' in file - workallotment.xlsm          taskswb.worksheets("may")                              ' in tasks.xlsx attached              'finding last rows             t_lastrow = .range("a1000000").end(xlup).row + 1             wa_lastrow = washeet.range("a1000000").end(xlup).row              'goes through names in tasks_sheet1             r = t_firstrow t_lastrow step 2                   set wa_namerng = washeet.range("a:a").find(.range("a" & r).value, _                 lookin:=xlvalues, lookat:=xlwhole, searchdirection:=xlnext, matchcase:=false)                  if not wa_namerng nothing                      wa_namerow = wa_namerng.row                      curtaskcol = 2                     wa_tmpcol = 2                      while not isempty(.cells(r, curtaskcol).value)                          c = 1 .cells(r + 1, curtaskcol).value                             washeet.cells(wa_namerow, wa_tmpcol).value = .cells(r, curtaskcol).value                             wa_tmpcol = wa_tmpcol + 1                         next c                          curtaskcol = curtaskcol + 1                      loop                  end if              next r          end          msgbox ("done")        r = t_firstrow t_lastrow step 2                ' loop find importance      col = 2                                               'setting initial col     curtaskcol = 17    ' position input - constituent jobs @ 17th col in tasks.xls      while not isempty(taskswb.worksheets("may").cells(r, curtaskcol).value)       str(curtaskcol - 16) = taskswb.worksheets("may").cells(r, curtaskcol).value             ' reading input first array of string element      substr = left(str(curtaskcol - 16), application.worksheetfunction.find("=", str(curtaskcol - 16)) - 1)         ' if t1+t2=t3 it'll before "=" symbol     msgbox (substr)     splitarray() = split(substr, "+")  ' if t1+t2 split t1 & t2       = lbound(splitarray) ubound(splitarray)          s(i + 1) = splitarray(i)          ' assigning split elements string array          next     = lbound(splitarray) ubound(splitarray)         col_new = 2                   ' checking 2nd column           while not isempty(taskswb.worksheets("may").cells(r, col_new).value)          if (s(i + 1) = taskswb.worksheets("may").cells(r, col_new).value)                 'initialising max , imps           imps = s(i + 1)       ' important job            max = taskswb.worksheets("may").cells(r + 1, col_new).value          end if                       ' maximum time taken task         col_new = col_new + 1         loop          j = lbound(splitarray) ubound(splitarray)            col_new = findcol(s(j + 1), r, taskswb)             if (max < taskswb.worksheets("may").cells(r + 1, col_new).value)           max = taskswb.worksheets("may").cells(r + 1, col_new).value           imps = taskswb.worksheets("may").cells(r, col_new).value          end if         next j          next     taskswb.worksheets("may").cells(r, curtaskcol + 6).value = imps                                 ' assign important task on 6th column current column     curtaskcol = curtaskcol + 1               ' runtime error 1004       loop        next r   end sub  public function findcol(s string, row integer, thewb workbook) integer     dim col integer, addr integer     col = 2                  ' checking column 2     'set taskswb = workbooks.open("e:/tasks.xlsx")       while not isempty(thewb.worksheets("may").cells(row, col).value)           if (strcomp(trim(s), trim(thewb.worksheets("may").cells(row, col).value)) = 0)            addr = col                         ' if task string found in column         end if         col = col + 1                            ' return column found         loop         findcol = addr end function 

krishnan,

in main proc workallotment declare variable taskswb.

in method 'findcol' reference taskswb. looks you've pulled code out of main proc. taskswb has scope within workallot , need give findcol object have within it's scope well.

i recommend pass taskswb method, third parameter.

your method follows.

edit comment of why findcol doesn't return. exit function ensure method exited after setting return value. without end in asking correct task name again.

public function findcol(s string, row integer, thewb workbook) integer     col = 2                  ' checking column 2     while not isempty(thewb.worksheets("may").cells(row, col).value)         if (s = thewb.worksheets("may").cells(row, col).value)                               findcol = col                         ' if task string found in column                                  exit function         end if         'msgbox ("enter correct task names")  not sure why here.         col = col + 1                            ' return column found     loop end function 

and you'd call

col_new = findcol(s(j + 1), r, taskswb)    ' error line function find column of task string 

this ensure not "leak" variable definition global scope, , ensure method doesn't depend on external globals.

edit 3: findcol still wrong.

public function findcol(s string, row integer, thewb workbook) integer     dim col integer     '******* don't need because can exit     'dim addr integer     col = 2                  ' checking column 2      '*****   line needs removed because using thewb being passed in *****     'set taskswb = workbooks.open("e:/tasks.xlsx")       while not isempty(thewb.worksheets("may").cells(row, col).value)          '****** line must use thewb         'if (strcomp(trim(s), trim(taskswb.worksheets("may").cells(row, col).value)) = 0)         if (strcomp(trim(s), trim(thewb.worksheets("may").cells(row, col).value)) = 0)              '*************  can exit once you've found need.             'addr = col                         ' if task string found in column             findcol = col             exit function                     end if         col = col + 1                            ' return column found     loop     ' can exit don't need this.     ' findcol = addr end function 

you should check when call function value hasn't returned 0, eg

new_col = findcol( .... ) if new_col = 0    msgbox "couldn't find column str" & s(j + 1) end if