CODEWITHSUNDEEP
vbaexcel
ghostlegend
ghostlegend

Reputation: 572

fill specific cells in second column - vba excel

This is my code: 

Sub fillcells()   

     Dim rngA As Range                  
     Dim arrayFinal As Variant                  
     Dim i As Long                  

     For Each rngA In Range("A:A") 

        If rngA.Value Like "*-*" Then         
            Debug.Print True         
            ' Split range A value into an array                 
            arrayFinal = Split(rngA.Value, "-")               
            'add n rows below the cells containig "-"                     
            rngA.Offset(1, 0).Resize(UBound(arrayFinal)).EntireRow.Insert Shift:=xlDown         
            For i = LBound(arrayFinal) To UBound(arrayFinal)         
                rngA.Offset(i, 0).Value = arrayFinal(i)         
            Next i   
      End If
     Next rngA  

End Sub

Editor note: the code is wrong since the If is inside a For loop but end (End If) after it (Next rngA)! Is it a typo or a mistake?

On the sheet I have 2 filled columns(A and B). My code loop column A and check the cells containing "-" character. If it finds it, adds a new row below that cell and fills it with the string after "-" from column A. But my problem is this: in column B I have much empty rows that I created before. I want to fill them with the strings over empty rows, from column B. How can I do that?

to be more specific, for example: 

         column B   

row1:    hello1         
row2:    (empty)         
row3:    (empty)                  
row4:    hello2         
row5:    hello3         
row6:    hello4         
row7:    (empty)      
row8:    hello5                     
...         
rown:    .....

and what I want is this:

row1:    hello1         
row2:    hello1         
row3:    hello1                 
row4:    hello2         
row5:    hello3         
row6:    hello4         
row7:    hello4      
row8:    hello5                     
...         
rown:    .....

What I need is an optimized code, so I have to use (if possible) the same loop because I have many filled rows.

Upvotes: 0

Views: 1528

Answers (1)

djikay
djikay

Reputation: 10648

I have modified your code (see below) to give you one way of doing what you want. It may not be the best way, but at least it'll give you an idea about what you can do.

Sub fillcells()

  Dim rngA As Range
  Dim rngB As Range ' <~~ NEW CODE
  Dim arrayFinal As Variant
  Dim i As Long

  For Each rngA In Range("A:A")

    If rngA.Value Like "*-*" Then
      Debug.Print True
      ' Split range A value into an array
      arrayFinal = Split(rngA.Value, "-")
      'add n rows below the cells containig "-"
      rngA.Offset(1, 0).Resize(UBound(arrayFinal)).EntireRow.Insert Shift:=xlDown
      For i = LBound(arrayFinal) To UBound(arrayFinal)
        rngA.Offset(i, 0).Value = arrayFinal(i)
      Next i
    End If

    ' NEW CODE STARTS HERE
    Set rngB = rngA.Offset(0, 1)
    If rngA.Value <> vbNullString And rngB.Value = vbNullString Then
      rngB.Value = rngB.Offset(-1, 0).Value
    End If
    ' NEW CODE ENDS HERE

  Next rngA

End Sub

To start with, I added a new Range variable (rngB). Then, I set it as a single column offset from rngA. Finally, if the cell column A has some text but in column B it's empty, then I copy the value of the previous row in column B to the current row in column B. And all within your existing loop, as you wanted.

Upvotes: 0

Related Questions