Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with find
I need help with nested find loop. I have 3-9 work sheets that have the same
formatted data on them just the numbers change. The sheets are named by the operator that took the data ie. ( jeff, ron, bill etc.) I need to check all the sheets in the work book and write them to a new sheet call Data Report. The data on the sheets looks like this. PART# 1 SC 001 Z AXIS X 6335.002 6335 0.002 1.5 -1.5 AXIS Y 830.991 831 -0.009 1.5 -1.5 AXIS Z 2250.631 2250.9 -0.269 1.5 -1.5 SC 002 X AXIS X 6311.002 6311 0.002 1.5 -1.5 AXIS Y 898.508 898.51 -0.002 1.5 -1.5 AXIS Z 2242.783 2243.35 -0.567 1.5 -1.5 SC 003 Y AXIS X 6255.289 6255.06 0.229 1.5 -1.5 AXIS Y 937.806 938.33 -0.524 1.5 -1.5 AXIS Z 2124.906 2125 -0.094 1.5 -1.5 PART# 2 SC 001 Z AXIS X 6335.002 6335 0.002 1.5 -1.5 AXIS Y 830.991 831 -0.009 1.5 -1.5 AXIS Z 2252.631 2250.9 -0.269 1.5 -1.5 SC 002 X AXIS X 6313.002 6311 0.002 1.5 -1.5 AXIS Y 898.508 898.51 -0.002 1.5 -1.5 AXIS Z 2242.783 2243.35 -0.567 1.5 -1.5 SC 003 Y AXIS X 6255.289 6255.06 0.229 1.5 -1.5 AXIS Y 937.806 938.33 -0.524 1.5 -1.5 AXIS Z 2124.906 2125 -0.094 1.5 -1.5 PART# 3 SC 001 Z AXIS X 6335.002 6335 0.002 1.5 -1.5 AXIS Y 830.991 831 -0.009 1.5 -1.5 AXIS Z 2251.631 2250.9 -0.269 1.5 -1.5 SC 002 X AXIS X 6312.002 6311 0.002 1.5 -1.5 AXIS Y 898.508 898.51 -0.002 1.5 -1.5 AXIS Z 2242.783 2243.35 -0.567 1.5 -1.5 SC 003 Y AXIS X 6255.289 6255.06 0.229 1.5 -1.5 AXIS Y 937.806 938.33 -0.524 1.5 -1.5 AXIS Z 2124.906 2125 -0.094 1.5 -1.5 Is what I need is to put each part # on a separate line with only the 1st set of numbers that correspond to the letter in the SC number. ex. (SC 001 Z in need the number beside the Axis Z€¦ 2250.631) There may be any ware from 3 SC points to more than 20 and any ware from 3 parts to more than 10. The final data should look like this. SC 001 SC 002 SC 003 jeff Part # 1 2250.631 6311.002 937.806 Part # 2 2252.631 6313.002 937.806 Part # 3 2251.631 6312.002 937.806 ron Part # 1 2250.631 6311.002 937.806 Part # 2 2252.631 6313.002 937.806 Part # 3 2251.631 6312.002 937.806 bill Part # 1 2250.631 6311.002 937.806 Part # 2 2252.631 6313.002 937.806 Part # 3 2251.631 6312.002 937.806 The persons name or the part name does not need to be on the report. I just put it there for clarity reasons. Anything you could give me would be a great help. Thanks Jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with find
Option Explicit
Sub Builder() Dim part As Long Dim sc As Long Dim partname As String Dim xyz As Long Dim wsSource As Worksheet Dim wsResult As Worksheet Dim targetrow As Long Set wsResult = GetResultSheet For Each wsSource In Worksheets If Left(wsSource.Range("B1").Value, 5) = "PART#" Then part = 0 Do partname = wsSource.Cells(part * 16 + 1, 2) targetrow = targetrow + 1 wsResult.Cells(targetrow, 1) = partname sc = 0 Do xyz = InStr("XYZ", wsSource.Cells(part * 16 + sc * 5 + 2, 2).Value) wsResult.Cells(targetrow, 2 + sc) = wsSource.Cells(part * 16 + sc * 5 + 2 + xyz, 3).Value sc = sc + 1 Loop While wsSource.Cells(part * 16 + sc * 5 + 2, 2).Value < "" part = part + 1 Loop While wsSource.Cells(part * 16 + 1, 2) < "" End If Next End Sub Private Function GetResultSheet() As Worksheet On Error Resume Next Set GetResultSheet = Worksheets("Data Report") If Err.Number < 0 Then Err.Clear Set GetResultSheet = Worksheets.Add(Worksheets(1)) End If On Error GoTo 0 With GetResultSheet .Cells.Clear .Name = "Data Report" End With End Function "Jeff" wrote: I need help with nested find loop. I have 3-9 work sheets that have the same formatted data on them just the numbers change. The sheets are named by the operator that took the data ie. ( jeff, ron, bill etc.) I need to check all the sheets in the work book and write them to a new sheet call Data Report. The data on the sheets looks like this. PART# 1 SC 001 Z AXIS X 6335.002 6335 0.002 1.5 -1.5 AXIS Y 830.991 831 -0.009 1.5 -1.5 AXIS Z 2250.631 2250.9 -0.269 1.5 -1.5 SC 002 X AXIS X 6311.002 6311 0.002 1.5 -1.5 AXIS Y 898.508 898.51 -0.002 1.5 -1.5 AXIS Z 2242.783 2243.35 -0.567 1.5 -1.5 SC 003 Y AXIS X 6255.289 6255.06 0.229 1.5 -1.5 AXIS Y 937.806 938.33 -0.524 1.5 -1.5 AXIS Z 2124.906 2125 -0.094 1.5 -1.5 PART# 2 SC 001 Z AXIS X 6335.002 6335 0.002 1.5 -1.5 AXIS Y 830.991 831 -0.009 1.5 -1.5 AXIS Z 2252.631 2250.9 -0.269 1.5 -1.5 SC 002 X AXIS X 6313.002 6311 0.002 1.5 -1.5 AXIS Y 898.508 898.51 -0.002 1.5 -1.5 AXIS Z 2242.783 2243.35 -0.567 1.5 -1.5 SC 003 Y AXIS X 6255.289 6255.06 0.229 1.5 -1.5 AXIS Y 937.806 938.33 -0.524 1.5 -1.5 AXIS Z 2124.906 2125 -0.094 1.5 -1.5 PART# 3 SC 001 Z AXIS X 6335.002 6335 0.002 1.5 -1.5 AXIS Y 830.991 831 -0.009 1.5 -1.5 AXIS Z 2251.631 2250.9 -0.269 1.5 -1.5 SC 002 X AXIS X 6312.002 6311 0.002 1.5 -1.5 AXIS Y 898.508 898.51 -0.002 1.5 -1.5 AXIS Z 2242.783 2243.35 -0.567 1.5 -1.5 SC 003 Y AXIS X 6255.289 6255.06 0.229 1.5 -1.5 AXIS Y 937.806 938.33 -0.524 1.5 -1.5 AXIS Z 2124.906 2125 -0.094 1.5 -1.5 Is what I need is to put each part # on a separate line with only the 1st set of numbers that correspond to the letter in the SC number. ex. (SC 001 Z in need the number beside the Axis Z€¦ 2250.631) There may be any ware from 3 SC points to more than 20 and any ware from 3 parts to more than 10. The final data should look like this. SC 001 SC 002 SC 003 jeff Part # 1 2250.631 6311.002 937.806 Part # 2 2252.631 6313.002 937.806 Part # 3 2251.631 6312.002 937.806 ron Part # 1 2250.631 6311.002 937.806 Part # 2 2252.631 6313.002 937.806 Part # 3 2251.631 6312.002 937.806 bill Part # 1 2250.631 6311.002 937.806 Part # 2 2252.631 6313.002 937.806 Part # 3 2251.631 6312.002 937.806 The persons name or the part name does not need to be on the report. I just put it there for clarity reasons. Anything you could give me would be a great help. Thanks Jeff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
find and delete duplicate entries in two columns or find and prin. | Excel Programming | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming |