excel macro to fill empty adjacent cells (SAP BOM report) with some condition

I have worksheet exported from SAP BOM. The wrok sheet contains first assembly and followed by childs and again assembly followed by childs and so on.

Now I have to fill the assembly items on the adjacent cells sothat I get assembly and child on one row.
example

before update
COL A__COL B
empty___assy1
v1______child11
v2______child12
v3______child13
empty___assy2
v4______child21
empty___assy3
v5______child31
v6______child32

Now after updation, the result shold be
COL A__COL B
empty___assy1
assy1______child11
assy1______child12
assy3______child13
empty___assy2
assy2______child21
empty___assy3
assy3______child31
assy3______child32


I need a macro to fill the assy values against the respective childs




LVL 7
karunamoorthyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AgeOfEmpiresCommented:
The macro below should work for you, given your example data.  This can also be accomplished via a formula, but since you've asked for a macro solution, that is what I've supplied.

One point of clarification, I am assuming on line 4 of your "result should be" data, that the you really meant for the assembly tied to child13 to be assy1 instead of assy3.

Also, my macro depends upon cells in column B being fully populated (no blanks) through the end of your data.  A slight modification to the code would be necessary if there is the possiblity of blanks within the data in column B.  The code also depends upon the first row of data being an assembly row (from your data discussions I would assume to always be the case).  Change the sheetname as necessary on the first executable line of my code (first line after DIM statements).

Hope this helps.  Let me know if you have questions.

Sub fillassembly()
  Dim rowndx As Integer
  Dim s1 As String, s2 As String, assembly As String
  Dim ptr As Range
  
  Set ptr = Worksheets("Sheet1").Range("A1")
  rowndx = 0
  assembly = ""
  
  s2 = Trim(ptr.Offset(rowndx, 1).Value)
  While s2 <> ""
    s1 = Trim(ptr.Offset(rowndx, 0).Value)
    If s1 = "" Then
      assembly = s2
    Else
      ptr.Offset(rowndx, 0).Value = assembly
    End If
    
    rowndx = rowndx + 1
    s2 = Trim(ptr.Offset(rowndx, 1).Value)
  Wend
  
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
karunamoorthyAuthor Commented:
Thank you for the immediate solution. I will try this and give you a feedback.
0
karunamoorthyAuthor Commented:
Thank you very much!
0
AgeOfEmpiresCommented:
Excellent!  Glad it was what you were looking for.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.