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?
|
Similar Threads
