Excel 2007 VBA find function. Trying to find data between two sheets and put it in a third sheet -

-

all,

i trying write macro search cells column 2 sheet1 in sheet2 , copy found rows sheet 2.

this have got far:

sub copyunique()    application.displayalerts = false     set qa_14 = sheets("qa 14feb")    set prod_14 = sheets("prod 14feb")    set prod_o14 = sheets("sheet1")    counter = 1     dim found range    dim qarange range    row = 1 prod_14.usedrange.rows.count         set qarange = qa_14.cells(2, 1)        set found = qarange.find(what:=prod_14.cells(row, 2).text, after:=qa_14.range("a1"), lookin:=xlvalues, lookat:=xlpart, searchorder:=xlbycolumns, searchdirection:=xlnext, matchcase:=false, searchformat:=false)         if not found nothing             prod_14.usedrange.range(cells(row, 1), cells(row, prod_14.usedrange.columns.count)).copy prod_o14.range("a" & ltrim(str(counter)))              counter = counter + 1        end if      next  end sub

the problem occurs on line find function. gives type mismatch error. have tried splitting variables separate line they're not part of problem.

any ideas?

thanks

the after parameter you're using invalid. remove , won't receive type mismatch error anymore. first question answered, , have other... i'm checking how can set value properly.

i believe maybe if give better explanation of you're trying achieve improve our assistance giving suggestions.

rgds

edit:

it seems after needs within range being searched (and believe that's not want).

this code not raise errors, although believe not want either. if give better example of need, may able further.

tip #1: next time when submitting code, i'd ask include in code declaration of variables you're using (you're using option explicit, right?). specially in type mismatch errors, variable type may cause problem.

tip #2: i'd suggest take on hungarian notation.

sub test()      dim qa_14 worksheet     dim prod_14 worksheet     dim prod_o14 worksheet     dim icounter integer     dim irow integer     dim rngafter excel.range     dim rngwhat excel.range      dim found range     dim qarange range      set qa_14 = sheets("qa 14feb")     set prod_14 = sheets("prod 14feb")     set prod_o14 = sheets("sheet1")     icounter = 1      irow = 1 prod_14.usedrange.rows.count          set qarange = qa_14.cells(2, 1)          set rngafter = qarange.cells(1, 1)          set found = qarange.find(what:=prod_14.cells(irow, 2).text, after:=rngafter, lookin:=xlvalues, lookat:=xlpart, searchorder:=xlbycolumns, searchdirection:=xlnext, matchcase:=false, searchformat:=false)          if not found nothing              prod_14.usedrange.range(cells(irow, 1), cells(irow, prod_14.usedrange.columns.count)).copy prod_o14.range("a" & ltrim(str(icounter)))              icounter = icounter + 1         end if      next  end sub

Comments

Post a Comment

Popular posts from this blog

Javascript line number mapping -

-
magically formatted comments change reported line number of javascript errors in browsers; this: //@line n "f" n line number , f file name. unfortunately, //@line appears ungoogleable. know there documentation on feature, , browsers support it? (i found references here , here .) from can find available in mozilla based browsers. it using js_setoptions command can turn these options on or off depending on values passed. https://developer.mozilla.org/en/js_setoptions
Read more

c# - Is it possible to remove an existing registration from Autofac container builder? -

-
something along lines: builder.registertype<mytype>().as<itype>(); builder.registertype<mytype2>().as<itype>(); builder.deregistertype<mytype>().as<itype>() var container = builder.build(); var types = container.resolve<ienumerable<itype>>(); assert.istrue(types.count == 1); assert.istrue(types[0].gettype == typeof(mytype2)); scenario: go through bunch of assemblies , go register types want make sure have 1 implementation of given type. need before create container. track on own nice if autofac me bit. this cannot done directly using containerbuilder , unless start on new one. mind you, having first built container should able construct new container filtering away unwanted types , reusing registrations first container. this: ... var container = builder.build(); builder = new containerbuilder(); var components = container.componentregistry.registrations .where(cr => cr.activator.limittype != typ...
Read more

php - Mysql PK and FK char(36) vs int(10) -

-
i have read mysql can handle indexes , searches better if tables keys integers rather char(36) uuid's. is worth converting int(10)? note: use 1 server , have have plans use multiple databases concurrently therefore removing 1 of big reasons using uuid. our tables innodb if makes difference. thanks in advance. usually rdbms can handle integer keys pk more efficient, other datatypes. reason how build index column, yes: long dont require string (or other typed) keys, should use integers. however: char(36) , int(10) far away being equal, because int(10) much smaller, char(36). dont know, if require many different keys, should keep in mind. update, complete last paragraph: int(10) 32-bit, char(36) 36 - ^byte^ (= 288-bit). not mean, int consumes less space, means, char(36) has 4 times more different keys.
Read more