I have a product that can have multiple variants and sizes. I need a macro that can take values and concatenate them to create product SKU . For example my product is 1234. The variants are ABF, PLC, MKLN, XTR. Sizes are 30, 36 etc. I need to to create all the variants in Macro using these values from a column. So my final products will be 1234-30-ABF, 1234-30-PLC, 1234-30-MKLN etc. and 1234-36-ABF,1234-36-PLC, etc. I can provide these values in column . I need to read the columns and run the macro in a loop using either & or concat function. I will provide the values . I tried the macro VBA but I cannot use variable in concat function . Please help.
ActiveCell.FormulaR1C1 = "=Sheet2!RC&""-""&Sheet2!RC[3]&"".""&""FR"""1 Answer
Assuming that the Base models are on Column A, the Variants on Column B, the Sizes on columns C and the SKUs when created are on different cells, then you can use the following VBA macro:
Sub AllSKU() Dim model, myvariant, mysize, sku As String Dim lRow As Long Dim i As Integer lRow = Cells(Rows.Count, 1).End(xlUp).Row 'Get the last row with data model = Range("A2").Value Range("E:E").Value = "" 'Clear Column E -- As it will be used for SKUs Range("E" & 1).Value = "SKU" For x = 2 To lRow If Range("A" & x).Value <> "" Then 'Get model when cell is not empty model = Range("A" & x) End If Range("E" & x).Value = model & "-" & Range("B" & x).Value & "-" & Range("C" & x).Value Next
End SubIt loops through the cells and creates SKUs on Column E.
