I have been trying to figure out for a week how I can get this to work. But in the example column G is the Position represented by numbers, Column H shows the parent for all the Childs or what each part goes inside. I cant think of a formula or formulas or VBA that can decode this. Any help is apricated. I have 750 files like this. I have attached a File.
PHP Code:Option Explicit
Sub decode()
Application.ScreenUpdating = False
Dim Lr&, k&, cell As Range, s, arr()
Lr = Cells(Rows.count, "A").End(xlUp).Row ' define last row of column A
ReDim arr(1 To Lr - 3, 1 To 1)
'Copy A:D to G:K
Range("G:K").Clear
Range("A1:D" & Lr).Copy Range("G1")
Range("H:H").Insert
Range("G:K").Columns.AutoFit
For Each cell In Range("G4:G" & Lr) ' loop through each cell in column G
k = k + 1
s = Split(cell, ".")
Select Case cell.Value
Case Is = ""
arr(k, 1) = ""
Case Is = "Position"
arr(k, 1) = "New Position"
Case Else
If UBound(s) = 0 Then ' Parent: level 1
arr(k, 1) = Range(Cells(1, "I"), cell.Offset(0, 2)).Find("Part #", , , , xlPrevious).Offset(1, 0)
Else ' Parent: level >1
arr(k, 1) = Range(Cells(1, "G"), cell).Find(Left(cell, Len(cell) - Len(s(UBound(s))) - 1), , , , xlPrevious).Offset(0, 2).Value
End If
End Select
Next
Range("H4").Resize(k, 1).Value = arr
Application.ScreenUpdating = True
End Sub
Quang PT
kpx4890,
Are you sure about the result in 2nd list of PL601007-30?
Please Login or Register to view this content.
The color dont matter and yes it is just a more complicated Bill of materials and there is a mistake at the end. I can fix it and show you. So the color was the show you where the numbers came from.
uploaded Book2.1
Thank you
Please Login or Register to view this content.
Hello, I have been trying to get this to with my original file but its giving me a bug error. Cell D1 is the cell i would use that was blue in my original file. for the single digit potion cell D1 value goes there. Thank you for the help, I tried a few variation to get the vba code to work but seems to only work in book 2
1) Which code are you referring to?
2) Why are you changing data layouts time to time?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks