VBA to transfer data on marker "y"

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
111
Office Version
  1. 2010
Platform
  1. Windows
Ladies, and Gents.
I had significant help on a VBA, however, I didn't take into account the aspect of merged cells, so I have gone back to the start and rectified this in the examples provided.
The aim of this is for the inspectors to operate of one sheet and the information is spread across multiple others. The inspectors will place a marker "y" in columns A and G of the INSPECTION sheet for the data to be transferred. This is explained further down.
I am needing this INSPECTION (please note the unmarked row in serial 1 as this is a possibility of the information) This information actually starts on row 14 in the workbook
Inspection trial.xlsx
ABCDEFGHIJKLM
1 REPAIR REQUIREDSerial NumberDESCRIPTION PACKAGECOMMENTSQTY RequiredParts to OrderP/NINSTALLEDDAMAGEDESTIMATED HOURS
2LEFT SIDE
3# 1 Skirt1166-2044FFyy
4y1Brace Lh #1 support1335Weld repair Lh #1 support to close & latch properly1071-9048RUyy0.5
5Washer Lh #1 support washer389-9683
61Washer Lh #1 support shim3y146-8960
7y2Washer #1 latch1301Fit washer & pin to Lh #1 latch free up & stake screw thread1y379-9572FFy0.25
82Pin #1 latch1y753-3893
9y3Stay #1 stop1337Fit Lh #1 stop stay correct way up, Replace shouldered screw1200-6049RUyy0.25
103Screw shouldered #1 stop2y071-9075
11y4Rubber Lh #1 rubber rear1302Reposition apply glue to Lh #1 rear rubber1FFy0.25
12# 2 1166-2045
13y5Bracket weldment Lh #2 bracket1360-61Weld repair Lh #2 lock pin bracket to open/close properly1y102-3466FFyy2
14y6Shim hinge upper1335-1336Adjust #1 & #2 to open close properly, Straighten re-align #2 rubber6y390-0053FFy0.25
156Spacer Shim Lh #2 lower hinge support1y102-3568
16# 31071-9044
17# 41071-9043
18Washer Lh #4 #5 lock block427Fit h/w to Lh # 4 locking pin block2379-9572RUy0.25
19Screw Lh #4 #5 lock block2390-0548RUy
20y7Bracket Lh/Rh #4 - #613,401,360Weld Lh #4 #5 #6 lock pin bracketsVariousFFyy2
21# 5 1071-9045
22y8# 6 1363Weld repair bent Lh #6 rubber mountings1071-9046RUyy1
23y9Rubber, Lh #6 rubber1318Replace damaged Lh #6 rubber, retainers & h/w1y208-7052RUyy0.5
249Strip, Lh #6 rubber retainer1102-4528
259Strip, Lh #6 retainer1y102-4522
269Screw, Lh #6 retainerAdjust #6 to open close properly, Straighten re-align #2 rubber14y068-0508
279Nut, Lh #6 retainer14y378-7761
28y10Hinge Butt # 6 piano hinge1344Weld fit missing piano hinge to position1y054-1578FFyy0.5
29# 7 ( Guard )1199-2407
30Rear Splash Guard1456-0508
31y11Screw rear splash guard1329Fit correct/missing h/w tap threads for Lh rear screws3y390-0547FFy0.25
3211Washer rear splash guard3y373-2547FFy
Sheet3

Turned to this REPORT using VBA. This example as seen actually starts on row 4
Inspection trial.xlsx
ABCDEFGHI
1SerialClassWork DescriptionPackageHRSP/NDescription Qty ReqRPS SOH
21RUWeld repair Lh #1 support to close & latch properly13350.50146-8960Washer Lh #1 support shim3
32FFFit washer & pin to Lh #1 latch free up & stake screw thread13010.25379-9572Washer #1 latch1
4753-3893Pin #1 latch1
53RUFit Lh #1 stop stay correct way up, Replace shouldered screw13370.25071-9075Screw shouldered #1 stop strap2
64FFReposition apply glue to Lh #1 rear rubber13020.25
75FFWeld repair Lh #2 lock pin bracket to open/close properly1360-612.00102-3466Bracket weldment Lh #2 lock bracket1
86FFAdjust #1 & #2 open close properly, Straighten re-align #2 rubber1335-13360.25390-0053Shim hinge upper6
9102-3568Spacer Shim Lh #2 lower hinge support1
107FFWeld Lh #4 #5 #6 lock pin brackets13,401,3602.00
118RUWeld repair bent Lh #6 rubber mountings13631.00
129RUReplace damaged Lh #6 rubber, retainers & h/w13180.50208-7052Rubber, Lh #6 rubber1
13Adjust #6 to open close properly, Straighten re-align #2 rubber102-4522Strip, Lh #6 retainer1
14068-0508Screw, Lh #6 retainer14
15378-7761Nut, Lh #6 retainer14
1610FFWeld fit missing piano hinge to position13440.50054-1578Hinge Butt # 6 piano hinge1
1711FFFit correct/missing h/w tap threads for Lh rear screws13290.25390-0547Screw rear splash guard3
18373-2547Washer rear splash guard3
Sheet4


The inspector will place a marker "y" in column A and give a serial number. They will then add any comments.
Any parts required will then have a marker "y" placed against them. If the part is not listed, they will insert a row and add it to the list

I am hoping that I might be able to get a VBA to do the following:
- Look in INSPECTION column A for the marker "y" ***ideally this marker is only need to be placed once against the serial number(please refer to first example)***

- Reference the serial number INSPECTION column B and list in REPORT column A (this serial only need to be listed once in the report, please refer to second example)
- Check for any comment in INSPECTION column E and list them in the REPORT column C
- Check for the reference in INSPECTION column J and list in REPORT column B
- NOW check for the marker "y" against ant parts in INSPECTION column G and list the following in REPORT
- P/N INSPECTION column H listed in REPORT column F
- Part Description in INSPECTION column C listed in REPORT column G
- Quantity Required in INSPECTION column F listed in REPORT column H
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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