VBA Find and Loop?

kpease01

New Member
Joined
Dec 14, 2016
Messages
1
Hi all,
I am new to the forum and have done basic looping and achieved success, however, my current project has me stumped. I have two workbooks, one lists the Project Manager, Customer name, Customer number and Totals for that specific customer. the other workbook is a total summary of all the Project managers, their customers, customer numbers and totals.

I am trying to open the first workbook, remember the data, activate the second workbook, find the matching PM, Customer number and Customer name, if those three criteria are met...paste the customer total into the correct column. if the customer doesn't exist I have it adding it to the last row, then sorting them all by PM at the end.

My problem is that I can have multiple PM's that could have the same customer, as their jobs do cross over sometimes.

I'm trying to open WORKBOOK 1, "remember" the PM, customer number and customer name in the first row,activate WORKBOOK 2, find the information, if they all match, paste, THEN return to WORKBOOK 1, move to the second row of that worksheet, remember the data, activate WORKBOOK 2, again, match the criteria, and paste or add to bottom etc. ..that's not working...grrr.

WORKBOOK 1
PMCUSTOMER NUMBER Customer NameTotals
Bryan Stoll2906 BASSETT REFRIGERATION23,270.00
Bryan Stoll3386 G & G MACHINE250.00
Bryan Stoll3766 GARDNER SYSTEMS CORP153,365.00
Bryan Stoll4250 ENERGY CONCEPTS CO9,370.00
Bryan Stoll4397 WM RENEWABLE ENERGY154,728.00
Bryan Stoll4678 EIRICH MACHINES INC126,140.00
<colgroup><col width="64" style="width: 48pt;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="64" style="width: 48pt;"> <col width="241" style="width: 181pt; mso-width-source: userset; mso-width-alt: 8813;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <tbody> </tbody>

<tbody>
</tbody>


WORKBOOK 2
PMCUSTOMER NUMBERCustomer NameTotals
Dave Nussbaum2377USA HOIST CO, INC.
Dave Nussbaum44VALLEY GRINDING & MANUFACTURING INC
Dave Nussbaum86VAN ERT ELECTRIC
Dave Nussbaum1594VAN ZEELAND MFG
Dave Nussbaum5248VINTON CONSTRUCTION COMPANY
Dave Nussbaum4141WHITE CONSTRUCTION INC
Dave Nussbaum4952ZARNOTH BRUSH WORKS INC
Dave Nussbaum5343FORK FARMS LLC
Dave Nussbaum2906BASSETT REFRIGERATION
Dave Nussbaum4570VALMET INC (NEENAH)
Dave Nussbaum4421VALMET INC (BELOIT)
Dave Nussbaum0VARIOUS
Michael Wilharm468VILTER MANUFACTURING CORP
Michael Wilharm2050MRL EQUIPMENT
Michael Wilharm4948SPACEX
Michael Wilharm4239PROSONIX LLC
Michael Wilharm2022SPX SHARED SERVICE/DOLLINGER
Mike Wyland164NEENAH FOUNDRY CO B0710
Mike Wyland16APPVION INC
Mike Wyland4311PRECISION THERMAL PROCESSING I
Mike Wyland5340ALLIED MECHANICAL WISCONSIN
Mike Wyland4082KIMBERLY-CLARK ACCOUNTS PAY
Mike Wyland74BEMIS COMPANY
Bryan Stoll4274KHS USA, INC
Bryan Stoll148METAL PRODUCTS
Bryan Stoll4161VACUUM, PUMP, & COMPRESSOR INC
Bryan Stoll2906BASSETT REFRIGERATION
Pat Wydeven3479SOLEX THERMAL SCIENCE INC
Pat Wydeven2581TANN CORPORATION
Pat Wydeven3386G & G MACHINE
Pat Wydeven122BEL BRANDS USA
Pat Wydeven406AZCO INC
Pat Wydeven1513A TO Z MACHINE
Pat Wydeven2147ACCURATE MACHINE
Pat Wydeven3306ACOUSTIC SPECIALITIES INC
Pat Wydeven2442ADVANCED TOOLING SPECIALIST
Pat Wydeven4538ANDRES MACHINE SERVICE
Pat Wydeven2601APPLETON STAINLESS INC.
Pat Wydeven3712APPLIED INDUSTRIAL
Pat Wydeven3031CARLSON & STEWART REFRIGERATION INC
Pat Wydeven2908BASSETT SHEET METAL FAB & SHIP
Pat Wydeven2381CUSTOM MARINE INC
Pat Wydeven0VARIOUS
Steve Barthel4082KIMBERLY-CLARK ACCOUNTS PAY
Steve Barthel4879GEA PROCESS ENGINEERING INC - MD
Steve Barthel4703SCHREIBER FOODS INC WEST
Steve Barthel4983VALMET LTD (THUNDER BAY)
Steve Barthel2906BASSETT REFRIGERATION
Steve Barthel4115BSP THERMAL SYSTEMS INC
Steve Barthel887CENTRAL GARDEN & PET KAYTEE/PETS INT'L
Steve Barthel101GREAT NORTHERN CONTAINER
Steve Barthel5207GREEN BAY MACHINERY
Steve Barthel4775GUSMER ENTERPRISES INC
Steve Barthel2686JACOBS FIELD SERVICES
Steve Barthel4783KIMBERLY AREA SCHOOL DISTRICT
Steve Barthel5227YAKFAB METALS INC
Terry Tipton1681EXPERA SPECIALTY SOLUTIONS LLC
Terry Tipton1998ALLIANCE LAUNDRY SYSTEMS
Terry Tipton5136BOURN & KOCH INC
Terry Tipton2044DIMENSION LUMBER COMPANY INC
Terry Tipton2679FEECO INTERNATIONAL
Terry Tipton2154GLOBAL REFRACTORY INSTALLER & SUPPLIES INC
Terry Tipton2134PIPING SERVICE
Terry Tipton2906BASSETT REFRIGERATION
Todd Wyland1607WAUPACA FOUNDRY
Todd Wyland Healy Arc
Todd Wyland74BEMIS COMPANY
<colgroup><col width="113" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4132;"> <col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;"> <col width="324" style="width: 243pt; mso-width-source: userset; mso-width-alt: 11849;"> <col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;"> <tbody> </tbody>

<tbody>
</tbody>
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top