Help with find

[Jeff]

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

[Patrick Molloy]

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
>
>