i wrote quick loop part of macro find cells in utwbk, based on list of values in dewbk. loop seems go fine few cells , crashes. problem is, crashes different values each time values exist in utwbk. here code:
dim utpath string dim utwbk workbook dim ogfund string dim ogcell range dim newfund string dim newcell range dim t long t = 2 tempfundlastrow if dewbk.sheets("macro").cells(t, 1).value <> "" set ogcell = dewbk.sheets("macro").cells(t, 1) ogfund = trim(ogcell.value) utwbk.sheets("report").range(cells(1, 1), range("aaa1").specialcells(xlcelltypelastcell)) set newcell = .find(ogfund, lookin:=xlvalues, lookat:=xlwhole) end newfund = newcell.value newcell.offset(2, 0).value = ogcell.offset(0, 8).value newcell.offset(3, 0).value = ogcell.offset(0, 9).value newcell.offset(4, 0).value = ogcell.offset(0, 11).value newcell.offset(6, 0).value = ogcell.offset(0, 10).value else 'nothing end if next t the code crashes run-time error 91: 'object variable of block variable not set' on line:
newfund = newcell.value in previous line define newcell, ogfund has value , can find value in utwbk not sure what's going on. assuming syntax .find incorrect not know how rectify this. usual, appreciated!
after line
set newcell = .find(ogfund, lookin:=xlvalues, lookat:=xlwhole) type this
if newcell nothing msgbox "not found" exit sub end if if see message box means .find couldn't find search text , since couldn't find, newcell.value break code newcell nothing
btw if word blah , in cell have blah leading , trailing spaces .find not find word because using lookat:=xlwhole. maybe want lookat:=xlpart
Comments
Post a Comment