olegvolf
11-30-2014, 11:41 PM
Hello,
I have this code that i am working with:
Public Sub COPYdimsfrom_TABLE() Sheets("data").Range("K1:N5000").ClearContents
Dim RNG1 As Range
Set RNG1 = Sheets("table").UsedRange.Find("Part", , xlValues, xlPart)
If Not RNG1 Is Nothing Then
MsgBox "Found in column " & RNG1.Column
Else
MsgBox "Not found", vbCritical
End If
ColNo = RNG1.Column
MsgBox Split(Cells(, ColNo).Address, "$")(1)
Collet = Split(Cells(, ColNo).Address, "$")(1)
TEST_COLUMN = Collet '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long
NextRow = 2
Dim serial As String
With Sheets("TABLE")
serial = InputBox("Supply the required Serial number")
serial = Right((serial), 2)
' MsgBox Right((serial), 2)
If serial> 0 Then
LastRow = .Cells(.Rows.count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow
If InStr(1, (.Cells(i, Collet).Value), serial) Then
LastRow = .Cells(.Rows.count, TEST_COLUMN).End(xlUp).Row
.Cells(i, "b").Resize(, 600).Copy
NextRow = NextRow + 1
With Sheets("data")
.Cells(NextRow, "k").PasteSpecial Paste:=xlPasteAll, Transpose:=True
Sheets("DATA").Range("K:k").NumberFormat = "0.0000"
End With
End If
Next i
End If
End With
End Sub
The macro is searching the column with the word "part" and then according to the inputted serial copies the data from the relevant row.
I have a problem with this when in the column i have 2 or 3 similar numbers.
How can i fix the code in order that the macro will take only the latest number in the column?
Thank you
Regards,
Oleg
I have this code that i am working with:
Public Sub COPYdimsfrom_TABLE() Sheets("data").Range("K1:N5000").ClearContents
Dim RNG1 As Range
Set RNG1 = Sheets("table").UsedRange.Find("Part", , xlValues, xlPart)
If Not RNG1 Is Nothing Then
MsgBox "Found in column " & RNG1.Column
Else
MsgBox "Not found", vbCritical
End If
ColNo = RNG1.Column
MsgBox Split(Cells(, ColNo).Address, "$")(1)
Collet = Split(Cells(, ColNo).Address, "$")(1)
TEST_COLUMN = Collet '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long
NextRow = 2
Dim serial As String
With Sheets("TABLE")
serial = InputBox("Supply the required Serial number")
serial = Right((serial), 2)
' MsgBox Right((serial), 2)
If serial> 0 Then
LastRow = .Cells(.Rows.count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow
If InStr(1, (.Cells(i, Collet).Value), serial) Then
LastRow = .Cells(.Rows.count, TEST_COLUMN).End(xlUp).Row
.Cells(i, "b").Resize(, 600).Copy
NextRow = NextRow + 1
With Sheets("data")
.Cells(NextRow, "k").PasteSpecial Paste:=xlPasteAll, Transpose:=True
Sheets("DATA").Range("K:k").NumberFormat = "0.0000"
End With
End If
Next i
End If
End With
End Sub
The macro is searching the column with the word "part" and then according to the inputted serial copies the data from the relevant row.
I have a problem with this when in the column i have 2 or 3 similar numbers.
How can i fix the code in order that the macro will take only the latest number in the column?
Thank you
Regards,
Oleg