Okay, I'm lost. I tried to modify your code to work with what I'm doing and
I think I messed it all up. What I am doing is taking the info from the list
on "Completed" and finding the same part number on "Cut List" and making
column S of that same row equal to the value of column F on "Completed"
This is the code I put in but I get an Overflow Error, whatever that is:
Sub Completed_Button_Click()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long
Dim c As Range
Dim strAnswer As String
Dim intStart As Integer
Dim lngEnd As Long
Dim intPart As Integer
Dim intPcs As Integer
Dim lngCnt As Long
Dim Wks As Worksheet
Set sh1 = Sheets("Completed")
Set sh2 = Sheets("Cut List")
lr = sh2.Cells(Rows.Count, 2).End(xlUp).Row
Set Rng = sh2.Range("B2:B" & lr)
Set c = Rng.Find(sh1.Range("C" & ActiveCell.Row).Value, LookIn:=xlValues)
Set Wks = ActiveSheet
intStart = 2
With Wks
lngEnd = .Range("A1").End(xlDown).Row
End With
For lngCnt = intStart To lngEnd
With Wks
intPart = .Range("C" & lngCnt).Value
intPcs = .Range("F" & lngCnt).Value
End With
If Not c Is Nothing Then
sh2.Range("S" & c.Row).Value = intPcs
End If
Next
End Sub
Matthew Herbert said:
A B C D
E F G
1 # Part Part File Production jobs Min Pcs Max
2 1 * 96032 96032x A36_075_7-22_1 24 * 24 24
3 2 * 10003 10003x A36_075_7-22_1 2 * 8 20
4 3 * 30005 30005x A36_075_7-22_1 10 * 17 36
5
6 Fab Time :30
7 Fab Date 8/14/09
* = Data I need
:
I have a list that can range in size from one to eighty components. The most
vital information from this list is in column B and it will always start at
row 2. After the list, there will be one blank row and then, for my purpose,
useless information. How would you do a loop that will retrieve the
information from column B and start to copy it to column L Row 10on until
that blank row in column B?
Thanks for everyones help, I really appreciate the help I have received to
this point.
please give more description.
as in
A B C
1 x x x
2
3 x y x
x=useless data
y=data you need
=blank- Hide quoted text -
- Show quoted text -
Since you know where the start of the loop is, there are a few ways to
get the end of the loop -- I've listed two below (see the included
comments). The code is untested and you'll need to potentially change
the data types of intPart and intPcs depending on what your data
actually is within the spreadsheet.
Matthew Herbert
Sub LoopIt()
Dim intStart As Integer
Dim lngEnd As Long
Dim intPart As Integer 'or whatever the data type is
Dim intPcs As Integer 'or whatever the data type is
Dim lngCnt As Long
Dim Wks As Worksheet
With Wks
'assuming the data is always anchored in A1
' and that there is a break in the data
' and the Fab Time
lngEnd = .Range("A1").End(xlDown).Row
'assuming the data is always anchored in A1
' and that the data is contiguous
lngEnd = .Range("A1").CurrentRegion.Rows.Count
End With
For lngCnt = intStart To lngEnd
With Wks
intPart = .Range("B" & lngCnt).Value
intPcs = .Range("F" & lngCnt).Value
'do something with the extracted data
MsgBox "The part is " & intPart & " and there are " _
& intPcs & " pieces."
End With
Next
End Sub- Hide quoted text -
- Show quoted text -
