Excel VBA: Loop through two columns in sheet1, look for specific names, paste rows with matching value to sheet2 -

-

context: new vba

task: have contact list in worksheet1 contains columns: lastname, firstname, email, phone #, , several more. have second contact list in worksheet2 (formatted same) contains approximately 500 of 1,000 names found in worksheet1 contact list updated contact information (email, phone #, etc.). i'm trying write code find names in both worksheets, , names, copy email, phone#, etc. worksheet2 (updated information) , paste corresponding location in worksheet2.

code: have far. not work.

 sub updatecontacts()   dim reference string  dim range range  dim contactlist worksheet  dim updatedcontacts worksheet   contactlist = activeworkbook.sheets("contact list")  updatedcontacts = activeworkbook.sheets("updated contacts")   reference = contactlist.range("b5", "c5").value   j = 5   = 5 updatedcontacts.cells(rows.count, 1).end(xlup).row        if updatedcontacts.range(cells(i, 2), cells(i, 3)).value = reference            updatedcontacts.range(cells(i, 4), cells(i, 17)).copy _            destination:=contactlist.range(cells(j, 4), cells(j, 17))            j = j + 1       end if     next end sub

any appreciated!

thanks

here working solution minor improvements such option explicit, qualified references @ times, option compare text ignore capital letters when comparing names, trim ignore possible leading or trailing spaces, , creating outer loop comparison names on shtcontactlist:

option explicit option compare text  sub updatecontacts()  dim ws worksheet dim rngcell range dim long, j long dim strreference string dim shtcontactlist worksheet dim shtupdatedcontacts worksheet  each ws in thisworkbook.worksheets     select case ws.name         case "contact list"             set shtcontactlist = ws         case "updated contacts"             set shtupdatedcontacts = ws         case else             debug.print ws.name     end select next ws if shtcontactlist nothing or shtupdatedcontacts nothing     msgbox "one or more required sheet(s) not found." & chr(10) & "aborting..."     exit sub end if  j = 5 shtcontactlist.cells(shtcontactlist.rows.count, "a").end(xlup).row     strreference = trim(shtcontactlist.cells(j, 2).value2) & ", " & trim(shtcontactlist.cells(j, 3).value2)     = 5 shtupdatedcontacts.cells(shtupdatedcontacts.rows.count, 1).end(xlup).row         if trim(shtupdatedcontacts.cells(i, 2).value2) & ", " & trim(shtupdatedcontacts.cells(i, 3).value2) = strreference             shtupdatedcontacts.range(shtupdatedcontacts.cells(i, 4), shtupdatedcontacts.cells(i, 17)).copy _                 destination:=shtcontactlist.range(shtcontactlist.cells(j, 4), shtcontactlist.cells(j, 17))             j = j + 1         end if     next next j  end sub

if code running slow might want consider using array: (1) put entire sheet shtupdatedcontacts array sheet shtcontactlist , (2) make search / comparison there. (3) finally, paste updates array sheet shtcontactlist.


Comments

Post a Comment

Popular posts from this blog

javascript - Slick Slider width recalculation -

-
i'm trying add slide in panel on slick container css. carousel works except new width not being calculated when space has been added. recalibrate once advance next slide i'd happen each time panel opened or closed. i've tried adding both margin , padding, neither makes difference. ideas? https://jsfiddle.net/mhigley/dpf7mlpl/ html: <button type="button" id="button"><i class="fa fa-arrow-right"></i></button> <aside> <h2>slide in panel</h2> <ol> <li>list item</li> <li>list item</li> <li>list item</li> <li>list item</li> <li>list item</li> </ol> </aside> <main role="main"> <div class="sections"> <section> <article> <h2>first slide</h2> <p>lorem ipsum d...
Read more

jsf - PrimeFaces Datatable - What is f:facet actually doing? -

-
looking through primefaces code in app noticed following line: <f:facet name="header">#{trainsearch.traincount} trains</f:facet> it looks overriding header, makes sense, can explain me in little more detail? what happening line of code? complete code listed below: <p:datatable id = "results" value = "#{trainsearch.trains}" var = "train" rendered="#{not empty trainsearch.trains}" styleclass = "train-search-table horizontal-border"> <f:facet name="header">#{trainsearch.traincount} trains</f:facet> <p:column headertext = "train id"> <p:panelgrid columns="1" styleclass = "train-id-grid" layout = "grid"> <h:outputtext styleclass = "train-id-label" value="#{train.traini}"/> <h:outputtex...
Read more

angular2 services - Angular 2 RC 4 Http post not firing -

-
i trying fire post request app , it's not working, tried multiple ways. don't see request being fired under network tab in chrome. not sure problem is, appreciated. import { injectable } '@angular/core'; import { http, headers } '@angular/http'; @injectable() export class userservice{ private _url="users"; constructor(private _http: http){ } checkifusernameexists(username){ return this._http.get(this._url+"/"+username) .map(response => response.json()) } createuser(user){ let headers = new headers(); headers.append('content-type', 'application/json') console.log(json.stringify(user)); return this._http.post(this._url, json.stringify(user),{headers: headers}) .map(response => response.json()) } } first need capture error , solve error: this._http.post(this._url, json.stringify(user),{headers: headers}) .subscribe( ...
Read more