• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

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




0
karunamoorthy
Asked:
karunamoorthy
  • 2
  • 2
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now