excel - Paste data from one workbook to another based on searching column for "C" or "D" -

excel - Paste data from one workbook to another based on searching column for "C" or "D" -

i have run roadblock. have document gets status given audit item (circle, triangle, x). currently, users have manually write problem on document. want auto populate other document based on selection in cell.

in example, cell string review v27:ad195. if of these cells include "c" or "d" homecoming value column "b" onto pfus sample document's next available empty cell.

i having problem programming thought getting work...i don't want/need re-create entire row cell in b column.

how upload example? original programming thought use

sub sample() dim wb1 workbook, wb2 workbook dim ws1 worksheet, ws2 worksheet dim copyfrom range dim lrow long '<~~ not integer. might give error in higher versions of excel dim strsearch string set wb1 = thisworkbook set ws1 = wb1.worksheets("test") strsearch = "d" ws1 '~~> remove filters .autofiltermode = false '~~> assuming names in col '~~> if not alter below whatever column letter lrow = .range("e" & .rows.count).end(xlup).row .range("e1:e" & lrow) .autofilter field:=1, criteria1:="=*" & strsearch & "*" set copyfrom = .offset(1, 0).specialcells(xlcelltypevisible).entirerow end '~~> remove filters .autofiltermode = false end '~~> destination file set wb2 = application.workbooks.open("c:\sample.xlsx") set ws2 = wb2.worksheets("sheet1") ws2 if application.worksheetfunction.counta(.cells) <> 0 lrow = .cells.find(what:="*", _ after:=.range("a1"), _ lookat:=xlpart, _ lookin:=xlformulas, _ searchorder:=xlbyrows, _ searchdirection:=xlprevious, _ matchcase:=false).row else lrow = 1 end if copyfrom.copy .rows(lrow) end wb2.save wb2.close end sub

but having problem manipulating work specific need because copies entire row. ideas? beginner vba here limited knowledge can pick quick.

teylyn may have been little abrupt complaint correct. making hard help posting big chunk of code not appear relate question. if had located piece of code not doing wanted/expected , created little illustration based on faulty code have received reply within minutes.

issues code

"in example, cell string review v27:ad195." "cell string" mean "cell range"? code performs autofilter on column e. how relate v27:ad195?

you want either "c" or "d" search "d".

i utilize autofilter , not expert. me, seems unusual way search multi-column range multiple values. search =*d*. understanding can either search particular string, or blanks or non-blanks. not think there wild card facility assume purpose of asterisks. not know why equals there.

if interested in column b, why set copyfrom = ... entirerow?

you have 2 separate requirements. (1) identify rows containing either "c" or "d". (2) move value of column b of each of rows worksheet. not check have achieved requirement 1 before attempting accomplish requirement 2.

my solution requirement 1

there many answers on stackoverflow show how move values 1 worksheet have ignored requirement 2.

requirement 1 trickier , have not seen similar question.

i not believe there sensible way of achieving requirement 1 autofilter. if knows different, interested in knowing how since means have misunderstood total capabilities of autofilter.

i have used vba search cell values believe find, search string within cell, faster. have not tested general advice not write vba duplicate excel functionality. there find all available keyboard there no vba equivalent. however, not believe vba find all helpful in case.

the first thing want code below is total of debug.print statements. did not write code in 1 go. stepped through code , used debug.print statements check each section did wanted before moving onto next section. there lot of debug.assert false 1 of has been commented out. when start, place debug.assert false statement @ head of every path through code. when 1 of these statements reached, execution stops. step on 1 statement , comment out debug.assert false. if there debug.assert false statement still active when have finished, either have not adequately tested code or design faulty , code cannot reached. either way, have more work do. there other ways of achieving same objectives these techniques work me.

your code have "c" , "d" , merge results. easier utilize array of search values in such cases duplicate code have:

searchvalue = array("c", "d", "z", "g")

you want "c" , "d" wanted test code. there no "z"s amid test info array allowed me test finish absence of value handled correctly.

i have 2 other arrays (rowfirst , rownext) size match searchvalue.

my test info is:

1 h j k l m g 2 h j d l m n 3 b c k e f 4 h j k l m n 5 o p q r s t u 6 v w x y x abcdef abc 7 def advertisement b e f g 8 h cab abd def l m n 9 c j k l m n 10 h j k l m n 11 h d k l m n 12 h g j k l m n 13 h g k l m n 14 h j d l m n 15 h j k l m n 16 h j d l m n

the first important block of code, searches first occurrences of 4 values , store values give:

searchvalue "c" "d" "z" "g" rowfirst 3 2 0 1 rownext 3 2 0 1

the code uses find repeatedly , loop. when, in the main loop, find tell me has found "c" on row 3 (the value in rowfirst), know has looped , every occurrence of "c" has been found , processed. rownext = 0 in "z" column tells code not "z"

the main loop first processes match found. lowest value in rownext 1 next (first) row 1 of these values. record 1 in array rowmatch.

the code updates rownext next rows containing search values after row 1. "c" , "d", next rows have been found. there no search "z". next "g" on row 7. arrays become:

searchvalue "c" "d" "z" "g" rowfirst 3 2 0 1 rownext 3 2 0 7

when find loops, rownext value set 0 indicate value finished. main loop continues until rownext values 0.

for test data, rows matching values (as stored in rowmatch) are:

1 2 3 6 7 8 9 11 12 13 14 16

if info matched mine , if interested in "g", these rows column b move new worksheet.

i hope above explanation, comments in code , output debug.print statements sufficient understand next code:

option explicit sub findmatchingrows() dim colrighttosearch long dim inxvaluecrnt long dim inxmatchcrnt long dim inxmatchmax long dim rngmatch range dim rowbottosearch long dim rngtosearch range dim rowfirst() long dim rowmatch() long dim rownext() long dim rowfirstcrnt long dim searchvalue() variant dim wshttosearch worksheet ' specify search values searchvalue = array("c", "d", "z", "g") ' define worksheet , range search. alter values set wshttosearch = worksheets("sheet1") set rngtosearch = wshttosearch.range("a1:z50") ' redim preserve slow statement not want utilize more ' necessary. when not know how many values want store in array ' start many entries think plenty , enlarge array ' if fill it. redim rowmatch(1 100) inxmatchmax = 0 ' no rows of values found yet ' 1 entry each entry on searchvalue ' search start after specified "after" cell, continues end of ' range, loops origin of range , continues "after" cell. ' rowfirst() used observe find looping , finding first row again. ' rownext() records recent search. redim rowfirst(lbound(searchvalue) ubound(searchvalue)) redim rownext(lbound(searchvalue) ubound(searchvalue)) ' identify bottom range , rightmost column of range searched. ' see below utilize made of these values rowbottosearch = rngtosearch.row + rngtosearch.rows.count - 1 colrighttosearch = rngtosearch.column + rngtosearch.columns.count - 1 debug.print "bottom right cell ("; rowbottosearch & ", " & colrighttosearch & ")" ' initialise rowfirst , rownext first row, if any, containing each ' search value. each search must start after bottom right cell of search ' range search starts in first cell of range rowfirstcrnt = 0 ' first row containing of values inxvaluecrnt = lbound(searchvalue) ubound(searchvalue) set rngmatch = rngtosearch.find(what:=searchvalue(inxvaluecrnt), _ after:=wshttosearch.cells(rowbottosearch, colrighttosearch), _ lookin:=xlvalues, lookat:=xlpart, searchorder:=xlbyrows, _ searchdirection:=xlnext) if rngmatch nil ' value not found within range 'debug.assert false ' not tested debug.print searchvalue(inxvaluecrnt) & " not found within range" rownext(inxvaluecrnt) = 0 else ' value found within range 'debug.assert false ' not tested debug.print searchvalue(inxvaluecrnt) & " found on row " & _ rngmatch.row & " in column " & rngmatch.column rownext(inxvaluecrnt) = rngmatch.row ' first row containing value rowfirst(inxvaluecrnt) = rngmatch.row if rowfirstcrnt = 0 ' first value found first row found matching value 'debug.assert false ' not tested rowfirstcrnt = rngmatch.row elseif rowfirstcrnt > rngmatch.row ' value found on before row previous best 'debug.assert false ' not tested rowfirstcrnt = rngmatch.row end if end if next debug.print "first rows: "; inxvaluecrnt = lbound(searchvalue) ubound(searchvalue) if rowfirst(inxvaluecrnt) = 0 'debug.assert false ' not tested debug.print " " & searchvalue(inxvaluecrnt) & " not found "; else 'debug.assert false ' not tested debug.print " " & searchvalue(inxvaluecrnt) & " on row "; rowfirst(inxvaluecrnt) & " "; end if next debug.print while rowfirstcrnt > 0 debug.print "next row match " & rowfirstcrnt ' record match inxmatchmax = inxmatchmax + 1 if ubound(rowmatch) < inxmatchmax 'debug.assert false ' not tested redim preserve rowmatch(1 100 + ubound(rowmatch)) end if rowmatch(inxmatchmax) = rowfirstcrnt ' farther matches rowfirstcrnt = 0 ' no match found far inxvaluecrnt = lbound(searchvalue) ubound(searchvalue) if rownext(inxvaluecrnt) = 0 ' either value not found or occurrences of value ' have been found , recorded 'debug.assert false ' not tested elseif rownext(inxvaluecrnt) > rowmatch(inxmatchmax) ' next occurrence of value after recent matching ' row still next occurrence of value if rowfirstcrnt = 0 ' next matching row 'debug.assert false ' not tested debug.print "first possible next match " & searchvalue(inxvaluecrnt) & _ " on row " & rownext(inxvaluecrnt) rowfirstcrnt = rownext(inxvaluecrnt) elseif rowfirstcrnt > rownext(inxvaluecrnt) ' value found on before row previous best 'debug.assert false ' not tested debug.print "new next match " & searchvalue(inxvaluecrnt) & _ " on row " & rownext(inxvaluecrnt) rowfirstcrnt = rownext(inxvaluecrnt) end if else 'debug.assert false ' not tested ' need search 1 time again starting @ end of rowmatch(inxmatchmax) ' note cannot utilize findnext because continues recent ' , code performing different finds set rngmatch = rngtosearch.find(what:=searchvalue(inxvaluecrnt), _ after:=wshttosearch.cells(rowmatch(inxmatchmax), colrighttosearch), _ lookin:=xlvalues, lookat:=xlpart, searchorder:=xlbyrows, _ searchdirection:=xlnext) if rngmatch nil ' should not possible since searching value know nowadays debug.assert false ' not tested else 'debug.assert false ' not tested debug.print searchvalue(inxvaluecrnt) & " found on row " & _ rngmatch.row & " in column " & rngmatch.column if rngmatch.row = rowfirst(inxvaluecrnt) ' have looped first occurrence. rows value ' found , recorded 'debug.assert false ' not tested rownext(inxvaluecrnt) = 0 debug.print searchvalue(inxvaluecrnt) & " has looped" else ' new value found 'debug.assert false ' not tested rownext(inxvaluecrnt) = rngmatch.row if rowfirstcrnt = 0 ' first value found first row found matching value 'debug.assert false ' not tested rowfirstcrnt = rngmatch.row elseif rowfirstcrnt > rngmatch.row ' value found on before row previous best 'debug.assert false ' not tested rowfirstcrnt = rngmatch.row end if end if ' process successful find end if ' process result of find end if ' decide if search value next inxvaluecrnt loop debug.print "rows matching values:"; inxmatchcrnt = 1 inxmatchmax debug.print " " & rowmatch(inxmatchcrnt); next debug.print end sub

excel vba excel-vba