VBA- how to duplicate values

Question

I'm having a bit of a problem with my VBA project. I have a list of unique values in column A (300 values, let's say) and in column B I need to triple each value (so I would get 900 values, each x3) and in column C I need to assign them: A, B, C, A, B, C, A, B, C, etc. so it would look like:

col A: val1 val2 val3 ...

col B: val1 val1 val1 val2 val2 val2 ...

col C: A B C A B C ...

I'm stuck on this, so I would appreciate some tips as I couldn't find anything even similar on stack, usually it's just removing duplicates

Do please include the code you used/tried. Also, this would be very easy in normal Excel-Formulae if you care to consider that option — JvdV

Anirudh Anirudh · Accepted Answer · 2020-05-15T10:46:22

Dim rowCount As Integer
Dim lrow As Long
Dim rowNum As Variant, element As Variant

lrow = ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
rowCount = 1
assignArrayValue = Array("A", "B", "C")
For rowNum = 1 To lrow
    For Each element In assignArrayValue
        ThisWorkbook.Worksheets(1).Cells(rowCount, "B").Value = ThisWorkbook.Worksheets(1).Cells(rowNum, "A").Value
        ThisWorkbook.Worksheets(1).Cells(rowCount, "C").Value = element
        rowCount = rowCount + 1
    Next
Next

I placed the values that you want repeated in the column A of the worksheet one below another. I have taken some static values such as the columns, worksheets and array as well, check and see if the above code helps in any way. Goodluck!

VBA- how to duplicate values

1 Answers