Replace characters in a string

Anthon · #1 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.

Pecoflyer · #2 11-03-2016, 12:48 AM

Hi
could you please, in the future, wrap your code with code tags (Go Adavnced- Select code - Click #)
I did it for you this time.
Thanks

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to do multiple find and replace in string.	PRA007	Word VBA	2	01-06-2016 09:10 PM
Use wildcards to replace some characters	mauuuuu5	Excel	2	12-12-2015 07:27 PM
find a set of characters in a string and return a 0 (zero) if not found	MaineLady	Excel	2	11-05-2015 03:23 PM
Retrieve characters after nth occurence of a string	veedee	Excel	5	06-16-2014 03:41 PM
Find and replace a string of text	errtu	Word	1	01-31-2013 02:09 PM