excel - If entered word is found in worksheet, then excute A, if not found, then excute B -


i tried write macro if enter 3-letter month abbreviation , it's found in working sheet, executed; else (if not found), let message box pop ("what entered doesn't match anything").

below code. works when there's match, when there's no match or wrong-typed 3-letter word, there's debug message - " run time error '91': object variable or block variable not set".

the line triggers error is:

 if sheets("sheet1").cells.find(what:=findmonth, _  lookin:=xlformulas, lookat:=xlwhole).column <> "0"

can tell what's wrong it? how can fix error? thank you.

sub cdaenter() dim rwcrow integer dim mscolumn1 integer dim mscolumn2 integer dim mscolumn3 integer dim mscolumn4 integer dim pdrrow1 integer dim pdrrow2 integer dim pdrrow3 integer dim pdrrow4 integer  dim mcolumn integer dim findmonth string  rwcrow = sheets("sheet2").cells.find(what:="rwc").row  mscolumn1 = sheets("sheet2").cells.find(what:="abc").column  mscolumn2 = sheets("sheet2").cells.find(what:="def").column  mscolumn3 = sheets("sheet2").cells.find(what:="ghi", lookin:=xlvalues, lookat:=xlwhole).column  mscolumn4 = sheets("sheet2").cells.find(what:="jkl").column  pdrrow1 = sheets("sheet1").cells.find(what:="mno").row pdrrow2 = sheets("sheet1").cells.find(what:="pqr").row pdrrow3 = sheets("sheet1").cells.find(what:="stu").row pdrrow4 = sheets("sheet1").cells.find(what:="vwx").row  findmonth = inputbox("enter 3-letter working month ")  if sheets("sheet1").cells.find(what:=findmonth, lookin:=xlformulas, lookat:=xlwhole).column <> "0"  mcolumn = sheets("sheet1").cells.find(what:=findmonth, lookin:=xlformulas, lookat:=xlwhole).column  sheets("sheet2").cells(rwcrow, mscolumn1).copy destination:=sheets("sheet1").cells(pdrrow1, mcolumn)  sheets("sheet2").cells(rwcrow, mscolumn2).copy destination:=sheets("sheet1").cells(pdrrow2, mcolumn)  sheets("sheet2").cells(rwcrow, mscolumn3).copy destination:=sheets("sheet1").cells(pdrrow3, mcolumn)  sheets("sheet2").cells(rwcrow, mscolumn4).copy destination:=sheets("sheet1").cells(pdrrow4, mcolumn)  exit sub  elseif iserror(mcolumn) = true msgbox "what entered doesn't match anything" exit sub end if  end sub

summing comments, i'll post answer (i know guys maybe busy post answer).
should of them decide post comment answer, i'll delete mine.

first declare variable:

dim r range ' find method returns range object, you'll need variable hold  set r = sheets("sheet1").cells.find(what:=findmonth, _         lookin:=xlformulas, lookat:=xlwhole)          'i copied above if statement since said works

as tim mentioned, check if found something:

if not r nothing     'you put rest of code here else     msgbox "what entered doesn't match anything.", vbinformation     'inform user through message box end if

if still have problems, in attempts, please add comment or revise question.
if entirely different issue, post another.


Comments

Post a Comment

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

Image
i have page in each contest shown in row. each row contains contest id , link other model names have foreign key contest. news model: class news(models.model): title = models.charfield(max_length=50, null=true) description = models.textfield() time = models.timefield( default= datetime.datetime.now().time()) contest = models.foreignkey(contest) i want use admin adding , changing these models. want when user clicks on 'news' has foreign key contest id=1, shows list of news value foreign key. use {% instance in instances %} <tr class="{% cycle 'row1' 'row2' %}"> <td>{{ instance.title }}</td> <td>{{ instance.starttime }}</td> <td>{{ instance.timespan }}</td> <td>{{ instance.date }}</td> <td><a href="/adm...
Read more

css - Make div keyboard-scrollable in jQuery Mobile? -

from can tell, although jquery-mobile-powered pages can contain divs overflow set scroll or auto, , these divs can scrolled one-screen bar or mouse wheel, cannot scrolled using arrow keys, page-up/page-down, or home/end. instead, official "page" div (with data-role="page") absorbs input. perhaps other divs can't acquire focus, i'm not sure. is there way around this? edit: jsfiddle of simple example: https://jsfiddle.net/qogz0shx/ <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.js"> </script> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"/> <style> #outer { overflow:scroll; height: 50vh; width: 50vw; } #inner { height: 500vh; width: 500vw; } ...
Read more

android - Keyboard hides my half of edit-text and button below it even in scroll view -

i having trouble keyboard covers edit-text , half of button in scroll view. here's layout , please tell me how should resolve problem. many questions on topic none of them s working me. <?xml version="1.0" encoding="utf-8"?> <linearlayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:background="@drawable/bg" android:layout_height="match_parent"> <scrollview android:layout_width="match_parent" android:fillviewport="true" android:layout_height="match_parent"> <relativelayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="vertical"> <linearlayout android:layout_width="match_parent" android:layout_height="w...
Read more