Microsoft Office Forums - View Single Post

Anthon · 11-02-2016, 10:24 PM

Hi guys,

I was wondering if you can help me with this problem. I would like to solve this in VBA. Attached is an excel file where Sheet1 is the current state and Sheet2 is what I would like to achieve. Basically, in column A, for each cluster of rows with similar Numbers I will need to insert the same cluster of rows below. The Number will subsequently change to reflect a new starting digit before "-". The Column A Numbers before "-" in the inserted rows will be tagged to the Description. For eg, if Description states "RECL FOR ABC" then the number before "-" should be 1. If Description states "RECL FOR XYZ" then the number before "-" should be 2. All digits after "-" is similar to before. In column B, the Value in the inserted rows will be the same as previously, but all to be negative. Finally, in column C, I would just like the exact copy of the Description in the above cells to be pasted in the inserted rows in the same column. You can refer to the excel if I am not doing a good job in explaining my problem. It will be nice to be able to program the highlighting colour for the inserted cells as well. Here's my code below:

Code:

Option Explicit
Sub DuplicateRowsInGroups()
Dim arrOLD As Variant, arrNEW As Variant
Dim Rw As Long, Col As Long, NewRw As Long, LR As Long, i As Long
Dim FR As Long, oldNUM As String, newNUM As String
LR = Range("A" & Rows.Count).End(xlUp).Row
arrOLD = Range("A21:I" & LR).Value
ReDim arrNEW(1 To LR * 2, 1 To 9)
NewRw = 1
For Rw = 1 To UBound(arrOLD)
    If FR = 0 Then
        FR = Rw
        oldNUM = arrOLD(Rw, 1)
        newNUM = Mid(oldNUM, InStr(oldNUM, "."), 100)
    End If
    
    For Col = 1 To 9
        arrNEW(NewRw, Col) = arrOLD(Rw, Col)
    Next Col
    NewRw = NewRw + 1
    If Rw = UBound(arrOLD) Then
        For i = FR To Rw
            arrNEW(NewRw, 1) = newNUM
            arrNEW(NewRw, 2) = -arrOLD(i, 2)
            arrNEW(NewRw, 3) = arrOLD(i, 3)
            arrNEW(NewRw, 4) = arrOLD(i, 4)
            arrNEW(NewRw, 5) = arrOLD(i, 5)
            arrNEW(NewRw, 6) = arrOLD(i, 6)
            arrNEW(NewRw, 7) = arrOLD(i, 7)
            arrNEW(NewRw, 8) = arrOLD(i, 8)
            arrNEW(NewRw, 9) = arrOLD(i, 9)
            NewRw = NewRw + 1
        Next i
        Exit For
    ElseIf arrOLD(Rw, 1) <> arrOLD(Rw + 1, 1) Then
        For i = FR To Rw
            arrNEW(NewRw, 1) = newNUM
            arrNEW(NewRw, 2) = -arrOLD(i, 2)
            arrNEW(NewRw, 3) = arrOLD(i, 3)
            arrNEW(NewRw, 4) = arrOLD(i, 4)
            arrNEW(NewRw, 5) = arrOLD(i, 5)
            arrNEW(NewRw, 6) = arrOLD(i, 6)
            arrNEW(NewRw, 7) = arrOLD(i, 7)
            arrNEW(NewRw, 8) = arrOLD(i, 8)
            arrNEW(NewRw, 9) = arrOLD(i, 9)
            NewRw = NewRw + 1
        Next i
        FR = 0
    End If
Next Rw
Range("A:A").NumberFormat = "@"
Range("A21:I21").Resize(UBound(arrNEW)).Value = arrNEW
End Sub

My code is able to address all the issues with Column B and C but not Column A. Can you help me to edit my code to address the Column A issue? Thanks in advance.