Excel list, VBA add from cells

1
votes

I am building a CSV-file of products to import to a webshop. As the products have different attributes (sizes and/or colors) I need to add these as variations in the file. In total I have around 800 rows and need to fill the blank spaces like shown in the picture below. The range of variations varies between three and five.

Screendump, Excel

I've been struggling to get some sort of VBA-code to work.

Sub FillVariations()
'
' Makro8 Makro
'

' Find row with content and copy

    Selection.End(xlDown).Select
    Selection.Copy

' Move one step down

    ActiveCell.Offset(1, 0).Activate

' Add =" <ctrl-v> " & " - " & az<samerow>

    ActiveCell.FormulaR1C1 = _
        "=""Axelväska/handväska, Crazy Horse, RCH7"" & "" - "" & RC[47]"

' Move one step down

    ActiveCell.Offset(1, 0).Activate

End Sub

Ctrl-V is the thought, but does not (obviously) work.

Basically: Move down one row. If the cell has contents - copy it. Move down one row. Paste the content, add a space, hyphen and another space then paste the content from column AZ on the same row. Move down one row. If the cell is empty, then paste the previously copied content, add space, hyphen and space and paste the AZ contents from the new row...

I'd greatly appreciate any help!

2
vbaexcel

2 Answers

0
votes

The main thing missing from your macro is a loop for the data, and a loop for the empty cells.

If you're planning to use this more than once, you might also want to select the correct column more carefully as it currently starts from whatever cell is selected.

Perhaps something like this:

Sub FillVariations()
' 
' Makro8 Makro
'
'figure out how far down data goes
Range("AZ2").Select
Selection.End(xlDown).Select
Dim endrow
endrow = Selection.Row

'always start in the correct column
Range("E1").Select
Selection.End(xlDown).Select


Dim basename

'loop through all data
Do While ActiveCell.Row < endrow
    'Store cell of current base name
    basename = Selection.Address

    'loop through empty cells and set formula if cell isn't empty
    Do While True
        ActiveCell.Offset(1, 0).Activate

        'if next cell isn't empty, isn't past the end of the list, go to outer loop
        If ActiveCell.Formula = "" And ActiveCell.Row <= endrow Then
            ActiveCell.Formula = "=CONCATENATE(" & basename & ","" - ""," & "AZ" & Selection.Row & ")"
        Else
            Exit Do
        End If
    Loop
Loop

End Sub
0
votes

One obvious problem with your approach is that there is a limit of how many characters can be stored in a worksheet cell (32,767 max?); this might be enough for you, of course, but if not...

Another thought is that you are trying to set it up like recording a macro of doing it "by hand" and not really automating the process.

Anyways, this (with whatever needed modifications) should do it efficiently. The sCSV string can be saved as a text/csv file.

Private Sub Build_String_CSV()
    Dim iRowStart As Long
    Dim iRowEnd As Long
    Dim i As Long
    Dim sCSV As String

    iRowStart = 1 ' or whatever
    iRowEnd = 800 ' or whatever
    ' Note that the values above can be found dynamically, of course
    For i = iRowStart To iRowEnd
            sCSV = sCSV & " - " & ActiveSheet.Range("AZ" & i)
    Next
    sCSV = sCSV = Mid(sC,SV, 4, 10000000) ' remove the very first 3 characters: space-dash-space
    ' Save the sCSV string as csv file
End Sub