Reputation: 572
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
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