PDA

View Full Version : Assigning a cell to a row that has a cell with a similar value



JayDP123
07-16-2010, 12:55 PM
Hey

I have a column filled with 5 digit values (00007,00085,00176).

I also have a column with path filenames which include that same number along with many other values (ABC12DE34_ABCD_12_00085_ABC.jpg). For example the "00085" would be the value in question.

These 2 values are not always on the same row. I would like to create a code that will ensure that the long file path name gets placed onto the same row as its corresponding 5-digit number.

This is important as I will be deleting the .jpg files, and would prefer not to delete the incorrect files!!

Thanks.
:dunno

Bob Phillips
07-16-2010, 01:46 PM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
.Columns(1).Insert
.Range("A1").Value2 = "RowNum"
.Range("A2").FormulaArray = "=MIN(IF(ISNUMBER(FIND(B2,C$2:C$" & LastRow & ")),ROW(C$2:C$" & LastRow & ")))"
.Range("A2").AutoFill .Range("A2").Resize(LastRow - 1)
.Columns("A:B").Resize(, 2).Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes
.Columns(1).Delete
End With

End Sub