Add next or previous letter with Excel VBA-CodePudding

I have data set like this

and I want to duplicate each row twice and add the next or previous letter in column "code". I am able to achieve the first goal (duplicate each row twice) but i am stuck to add the next or previous letter in column "code".

This i what I did :

Sub mysub()

Dim r As Range, n As Long, i As Long
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws1, ws2 As Worksheet
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")

Set r = ws1.Range("C3", ws1.Range("E" & Rows.Count).End(xlUp))

For i = 1 To r.Rows.Count
    n = n   1
    ws2.Cells(n   1, 1).Value = r.Cells(i, 1).Value
    ws2.Cells(n   1, 2).Value = r.Cells(i, 2).Value
    ws2.Cells(n   1, 3).Value = r.Cells(i, 3).Value
    n = n   1
    ws2.Cells(n   1, 1).Value = r.Cells(i, 1).Value
    ws2.Cells(n   1, 2).Value = r.Cells(i, 2).Value * -1
    
Next i


End Sub

and i get this

but I want to get this :

Some help would be appreciated

CodePudding user response：

You were pretty close, only need one line added.

You can slightly shorten the code using Resize.

Sub mysub()

Dim r As Range, n As Long, i As Long
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws1 As Worksheet, ws2 As Worksheet 'need to specify each

Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")

Set r = ws1.Range("C3", ws1.Range("C" & Rows.Count).End(xlUp))

For i = 1 To r.Rows.Count
    n = n   1
    ws2.Cells(n   1, 1).Resize(2, 2).Value = r.Cells(i, 1).Resize(, 2).Value
    ws2.Cells(n   1, 3).Value = r.Cells(i, 3).Value
    n = n   1
    ws2.Cells(n   1, 2).Value = r.Cells(i, 2).Value * -1
    ws2.Cells(n   1, 3).Value = IIf(r.Cells(i, 3) = "C", "D", "C") 'added
Next i

End Sub

CodePudding user response：

I would do the following: Copy the original range to sheet2, then loop backwards and duplicate each row and adjust values.

This should be a bit faster than handling each cell individually. You can get it even faster using arrays.

Option Explicit

Public Sub mysub()
    Dim wb As Workbook
    Set wb = ThisWorkbook

    Dim ws1 As Worksheet
    Set ws1 = wb.Sheets("Sheet1")
    
    Dim ws2 As Worksheet
    Set ws2 = wb.Sheets("Sheet2")

    Dim r As Range
    Set r = ws1.Range("C3", ws1.Range("E" & Rows.Count).End(xlUp))
    
    
    'copy from ws1 to ws2
    ws2.Range("A2").Resize(r.Rows.Count, r.Columns.Count).Value = r.Value
    
    'duplicate values: loop backwards when inserting or deleting
    Dim i As Long
    For i = r.Rows.Count To 1 Step -1
        ' duplicate row
        ws2.Rows(i   1).Copy
        ws2.Rows(i   1).Insert xlShiftDown
        
        ' adjust value *-1
        ws2.Rows(i   1).Cells(1, 2).Value = ws2.Rows(i   1).Cells(1, 2).Value * -1
        
        ' adjust D/C
        If ws2.Rows(i   1).Cells(1, 3).Value = "D" Then
            ws2.Rows(i   1).Cells(1, 3).Value = "C"
        Else
            ws2.Rows(i   1).Cells(1, 3).Value = "D"
        End If
    Next i
    
    Application.CutCopyMode = False
End Sub