I am new to Visual Basic Programming. I need to write a macro that will
search through a large set of data and labels to find the data I need and
report it into an organized form on another worksheet in the same workbook.
This is a part of what the data looks like:

LA BEL: M_ITEM- 12_REAR_1
Po ints Taken: 1
Actual Nominal #NAME? #NAME? Dev Out/Tol
Y: 362.3872 362.7 0.7 -0.7 -0.3128 **********


LA BEL: C_ITEM- 12_FRONT_2
Actual Nominal #NAME? #NAME? Dev Out/Tol
Y: 342.0504 342.5 0.7 -0.7 -0.4496 **********
L: 376.074 342.5


I need the macro to ignore all the text and find the data under the "Actual"
column that has an associated numeric value in the column that is labeled
"#NAME?" and copy that value into a cell on another worksheet. The macro
should continue running until the data ends. Can anyone help me write a
macro that will do this?

I don't know if I got the columns correct. the code looks down column B for
the word Actual. Then starts looking at each row of number until it finds an
empty cell in column B. If it finds an empty cell in column b it again looks
for the work actual beffore looking at the numbers again.

When it looks at the number in column b it checks for data in column D and
if there is data it will copy column D to sheet 2.


Enum FindState
FindActual = 1
FindData = 2
End Enum
Sub ExtractData()

NewRow = 1
With Sheets("Sheet1")
State = FindActual
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
RowCount = 1
State = FindActual
For RowCount = 1 To LastRow

Select Case State
Case FindActual:
If UCase(.Range("B" & RowCount)) = "ACTUAL" Then
State = FindData
End If
Case FindData
If .Range("B" & RowCount) = "" Then
State = FindActual
Else
If .Range("D" & RowCount) < "" Then
Sheets("Sheet2").Range("A" & NewRow) = .Range("D" &
RowCount)
NewRow = NewRow + 1
End If
End If
End Select
Next RowCount
End With
End Sub

"amboo_radley" wrote:

I am new to Visual Basic Programming. I need to write a macro that will
search through a large set of data and labels to find the data I need and
report it into an organized form on another worksheet in the same workbook.
This is a part of what the data looks like:

LA BEL: M_ITEM- 12_REAR_1
Po ints Taken: 1
Actual Nominal #NAME? #NAME? Dev Out/Tol
Y: 362.3872 362.7 0.7 -0.7 -0.3128 **********


LA BEL: C_ITEM- 12_FRONT_2
Actual Nominal #NAME? #NAME? Dev Out/Tol
Y: 342.0504 342.5 0.7 -0.7 -0.4496 **********
L: 376.074 342.5


I need the macro to ignore all the text and find the data under the "Actual"
column that has an associated numeric value in the column that is labeled
"#NAME?" and copy that value into a cell on another worksheet. The macro
should continue running until the data ends. Can anyone help me write a
macro that will do this?

Joel

Would your post below work in my situation?

I am using Visual 6.5

I am trying to get my worksheet to find the match of a number in column SUPC
on workbook 1, in a SUPC column in workbook 2. When it finds that number, I
want to to return the number in Price column of workbook 2 into Cost column
on worksheet 1. I think it would go something like this

Workbook 1
A B C
SUPC LB/CS COST
4773214 LB
4937397 LB


Workbook 2
A B C D E F
SUPC ITEM # SIZE ITEM_DESCRIPTION CS Price
4773214 2911284 LB 12.45
4937397 2611212 LB 12.51

Can anyone help here.

--
Jeff


"Joel" wrote:

I don't know if I got the columns correct. the code looks down column B for
the word Actual. Then starts looking at each row of number until it finds an
empty cell in column B. If it finds an empty cell in column b it again looks
for the work actual beffore looking at the numbers again.

When it looks at the number in column b it checks for data in column D and
if there is data it will copy column D to sheet 2.


Enum FindState
FindActual = 1
FindData = 2
End Enum
Sub ExtractData()

NewRow = 1
With Sheets("Sheet1")
State = FindActual
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
RowCount = 1
State = FindActual
For RowCount = 1 To LastRow

Select Case State
Case FindActual:
If UCase(.Range("B" & RowCount)) = "ACTUAL" Then
State = FindData
End If
Case FindData
If .Range("B" & RowCount) = "" Then
State = FindActual
Else
If .Range("D" & RowCount) < "" Then
Sheets("Sheet2").Range("A" & NewRow) = .Range("D" &
RowCount)
NewRow = NewRow + 1
End If
End If
End Select
Next RowCount
End With
End Sub

"amboo_radley" wrote:

I am new to Visual Basic Programming. I need to write a macro that will
search through a large set of data and labels to find the data I need and
report it into an organized form on another worksheet in the same workbook.
This is a part of what the data looks like:

LA BEL: M_ITEM- 12_REAR_1
Po ints Taken: 1
Actual Nominal #NAME? #NAME? Dev Out/Tol
Y: 362.3872 362.7 0.7 -0.7 -0.3128 **********


LA BEL: C_ITEM- 12_FRONT_2
Actual Nominal #NAME? #NAME? Dev Out/Tol
Y: 342.0504 342.5 0.7 -0.7 -0.4496 **********
L: 376.074 342.5


I need the macro to ignore all the text and find the data under the "Actual"
column that has an associated numeric value in the column that is labeled
"#NAME?" and copy that value into a cell on another worksheet. The macro
should continue running until the data ends. Can anyone help me write a
macro that will do this?

Sub GetPrice()

With Workbooks("Workbook1.xls").Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) < ""
SUPC = .Range("A" & RowCount)
With Workbooks("Workbook2.xls").Sheets("Sheet1")
Set c = .Columns("A").Find(what:=SUPC, _
LookIn:=xlValues, lookat:=xlWhole)
End With
If Not c Is Nothing Then
.Range("C" & RowCount) = c.Offset(0, 4)
End If
RowCount = RowCount + 1
Loop
End With
End Sub


"bhbjk1" wrote:

Joel

Would your post below work in my situation?

I am using Visual 6.5

I am trying to get my worksheet to find the match of a number in column SUPC
on workbook 1, in a SUPC column in workbook 2. When it finds that number, I
want to to return the number in Price column of workbook 2 into Cost column
on worksheet 1. I think it would go something like this

Workbook 1
A B C
SUPC LB/CS COST
4773214 LB
4937397 LB


Workbook 2
A B C D E F
SUPC ITEM # SIZE ITEM_DESCRIPTION CS Price
4773214 2911284 LB 12.45
4937397 2611212 LB 12.51

Can anyone help here.

--
Jeff


"Joel" wrote:

I don't know if I got the columns correct. the code looks down column B for
the word Actual. Then starts looking at each row of number until it finds an
empty cell in column B. If it finds an empty cell in column b it again looks
for the work actual beffore looking at the numbers again.

When it looks at the number in column b it checks for data in column D and
if there is data it will copy column D to sheet 2.


Enum FindState
FindActual = 1
FindData = 2
End Enum
Sub ExtractData()

NewRow = 1
With Sheets("Sheet1")
State = FindActual
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
RowCount = 1
State = FindActual
For RowCount = 1 To LastRow

Select Case State
Case FindActual:
If UCase(.Range("B" & RowCount)) = "ACTUAL" Then
State = FindData
End If
Case FindData
If .Range("B" & RowCount) = "" Then
State = FindActual
Else
If .Range("D" & RowCount) < "" Then
Sheets("Sheet2").Range("A" & NewRow) = .Range("D" &
RowCount)
NewRow = NewRow + 1
End If
End If
End Select
Next RowCount
End With
End Sub

"amboo_radley" wrote:

I am new to Visual Basic Programming. I need to write a macro that will
search through a large set of data and labels to find the data I need and
report it into an organized form on another worksheet in the same workbook.
This is a part of what the data looks like:

LA BEL: M_ITEM- 12_REAR_1
Po ints Taken: 1
Actual Nominal #NAME? #NAME? Dev Out/Tol
Y: 362.3872 362.7 0.7 -0.7 -0.3128 **********


LA BEL: C_ITEM- 12_FRONT_2
Actual Nominal #NAME? #NAME? Dev Out/Tol
Y: 342.0504 342.5 0.7 -0.7 -0.4496 **********
L: 376.074 342.5


I need the macro to ignore all the text and find the data under the "Actual"
column that has an associated numeric value in the column that is labeled
"#NAME?" and copy that value into a cell on another worksheet. The macro
should continue running until the data ends. Can anyone help me write a
macro that will do this?