excel - Scraping a worksheet for data and pasting to a separate worksheet -

this short script breaks @ 'next'. i'd save data in collection can dump , customize how it's presented across workbook. help

edit: updated code. still running issues.

resource class

'''''''''''''''''''''' ' name property '''''''''''''''''''''' public property name() string     name = pname end property public property let name(value string)     pname = value end property  '''''''''''''''''''''' ' city property '''''''''''''''''''''' public property city() string     city = pcity end property public property let city(value string)     pcity = value end property  '''''''''''''''''''''' ' title property '''''''''''''''''''''' public property title() string     title = ptitle end property public property let title(value string)     ptitle = value end property 

the script

    sub searchresources()   dim range dim cell variant dim resources collection dim emp resource dim count integer     each cell in a.rows     if cell.value = "dallas" or cell.value = "oklahoma city" or cell.value = "houston"     set emp = new resource         emp.city = cell.value         cell.offset(0, -2).select         emp.title = cell.value         cell.offset(0, -1).select         emp.name = cell.value             resources.add emp                     end if  resume next  each emp in resources  activeworkbook.sheets("a").activate a.select     debug.print emp.name     debug.print emp.city     debug.print emp.title next emp  end sub 

for each cell in a.rows     if cell.value = "dallas"     set emp = new resource         emp.city = cell.value         cell.offset(0, -2).select         emp.title = cell.value         cell.offset(0, -1).select         emp.name = cell.value             resources.add emp              >> resume next      elseif cell.value = "oklahoma city"     set emp = new resource         emp.city = cell.value         cell.offset(0, -2).select         emp.title = cell.value         cell.offset(0, -1).select         emp.name = cell.value             resources.add emp             >> resume next      elseif cell.value = "houston"     set emp = new resource         emp.city = cell.value        cell.offset(0, -2).select         emp.title = cell.value         cell.offset(0, -1).select         emp.name = cell.value             resources.add emp           >> resume  next              end if >>> next  each emp in resources  activeworkbook.sheets("a").activate a.select     debug.print emp.name     debug.print emp.city     debug.print emp.title next emp  end if <<<why here have end if? think should delete it, cause doesnt have if stand   update 

i think script got long , not need repeat @ same

for each cell in a.rows     if cell.value = "dallas" or cell.value = "oklahoma city" or cell.value = "houston"     set emp = new resource         emp.city = cell.value         cell.offset(0, -2).select         emp.title = cell.value         cell.offset(0, -1).select         emp.name = cell.value             resources.add emp                   end if else 'based on want exit or resume next next  each emp in resources  activeworkbook.sheets("a").activate a.select     debug.print emp.name     debug.print emp.city     debug.print emp.title next emp