Поле Excel / VBA Breakdown

Немного сложной задачи, которую я должен сделать, но я попытаюсь объяснить. У меня есть файл excel с 23000 строками данных, которые я импортирую на веб-сайт. У каждого из них есть поле:

Category | other data | other data 2 Foods/Dog/Treats Pre-Pack | 1223 | image.jpg 

Мне нужно это, чтобы захватить каждую строку и добавить новую строку под ней для каждого «/», чтобы превратить это в:

 Category | other data | other data 2 [blank in original line] | 1223 | image.jpg Foods | [blank field] | [blank field] Foods/Dog | [blank field] | [blank field] Foods/Dog/Treats Pre-Pack | [blank field] | [blank field] 

Таким образом, сценарий должен добавить новую строку для каждой категории, но сохранить перед собой оригинальную категорию. Таким образом, category/category2/category 3 превращается в 4 строки: [blank] - category - category/category2 - category/category2/category 3

Кто-нибудь знает способ или сценарий для этого?

Спасибо, Саймон

Примечание. Рабочий лист называется «тест», а столбец категории начинается с E2 и переходит на E23521

У меня есть следующий скрипт:

 Sub test() Dim a, i As Long, ii As Long, e, n As Long Dim b(), txt As String, x As Long With Range("a1").CurrentRegion a = .Value txt = Join$(Application.Transpose(.Columns(5).Value)) With CreateObject("VBScript.RegExp") .Global = True .Pattern = "/" x = .Execute(txt).Count * 2 End With ReDim b(1 To UBound(a, 1) + x, 1 To UBound(a, 2)) For i = 1 To UBound(a, 1) If a(i, 5) <> "" Then For Each e In Split(a(i, 5), "/") n = n + 1 For ii = 1 To UBound(a, 2) b(n, ii) = a(i, ii) Next b(n, 5) = Trim$(e) Next End If Next .Resize(n).Value = b End With End Sub 

Кажется, это создает новую строку, в которой я нуждаюсь, но не сохраняет структуру слэш, перемещающуюся с каждым из них. А также dosnt добавить пустую строку на все новые и сделать исходное значение категории пустым.

РЕШИТЬ:

 Sub splitEmUp() Dim splitter() As String 'this is storage space for the split function Dim i As Integer ' main-loop for counter "which cell we are on" Dim j As Integer ' splitter for-loop counter "which section of the split are we on" Range("E2").Activate 'starting in cell e2 because row 1 is headers and category is located in the B column For i = 0 To 24000 'from beginning to end i=0 means e2, i=1 means e3 ActiveCell.Offset(i, 0).Value = Replace(ActiveCell.Offset(i, 0).Value, " / ", "[email protected]#") splitter = Split(ActiveCell.Offset(i, 0), "/") 'split the cell based on / and store it in splitter If (UBound(splitter)) > 0 Then 'if a split occurred ActiveCell.Offset(i, 0).Value = "" 'set the activecell to blank Debug.Print i ActiveCell.Offset(i + 1, 0).EntireRow.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove 'insert a new row and shift everything down ActiveCell.Offset(i + 1, 0).Value = splitter(0) 'initialize the "Down" cells ActiveCell.Offset(i + 1, 0).Value = Replace(ActiveCell.Offset(i + 1, 0).Value, "[email protected]#", " / ") For j = 1 To UBound(splitter) ActiveCell.Offset(i + j + 1).EntireRow.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove 'create another row if it needs to ActiveCell.Offset(i + (j + 1), 0).Value = ActiveCell.Offset(i + j).Value & "/" & splitter(j) 'fill out the new row ActiveCell.Offset(i + (j + 1), 0).Value = Replace(ActiveCell.Offset(i + (j + 1), 0).Value, "[email protected]#", " / ") Next i = i + UBound(splitter) + 1 'need to step I past the new cells ReDim splitter(0) Erase splitter 'erase and eliminate splitter to avoid carry over. End If Next End Sub 

Это то, что я придумал. Обязательно измените имена листов, чтобы они соответствовали вашей книге. Также не забудьте изменить диапазон ввода, чтобы он соответствовал вашему собственному диапазону ввода ячеек.

 Function SplitAndWrite(inputCell As Range, TopOfOutputRange As Range, sep As String) As Range Dim texts() As String, i As Integer, outputText As String texts = Split(inputCell.Value, sep) outputText = "" TopOfOutputRange = "" 'your blank line For i = LBound(texts) To UBound(texts) outputText = outputText & sep & texts(i) TopOfOutputRange.Offset(i + 1) = outputText Next i Set SplitAndWrite = TopOfOutputRange.Offset(UBound(texts) + 1) End Function Sub THEPOPULATOR() Dim s3 As Worksheet, s4 As Worksheet Set s3 = Sheets("Sheet1") Set s4 = Sheets("Sheet2") Dim inputrange As Range, c As Range, outputrange As Range Set outputrange = s4.Range("A1") Set inputrange = s3.Range(s3.Cells(2, 1), s3.Cells(2, 1).End(xlDown)) 'cells(2,1) = "A1". change this to your top input cell. then the second half will find the bottom cell on its own. This is the same as pressing Ctrl+down For Each c In inputrange s3.Range(c.Offset(0, 1), c.Offset(0, c.End(xlToRight).Column)).Copy outputrange.Offset(1, 1) Set outputrange = SplitAndWrite(c, outputrange.Offset(1), "/") Next c End Sub 

Вот пример из другого решения. Как разделить ячейку в строке с Excel , которую я изменил чуть-чуть, чтобы соответствовать вашей ситуации:

 Public Sub solutionJook() Dim arr() As Variant Dim arrSum() As Variant Dim arrResult() As Variant Dim arrTemp As Variant Dim i As Long Dim j As Long Dim h As Long Dim lngSplitColumn As Long 'input of array to seperate -> should cover all columns+rows of your data arr = Range("A1:C2") 'specify which column has the values to be split up -> here this is the category column lngSplitColumn = 2 'using the boundries of the given range, 'arrSum has now always the right boundries for the first dimension ReDim Preserve arrSum(LBound(arr, 2) To UBound(arr, 2), 1 To 1) 'create the array with seperated ABC For i = LBound(arr, 1) To UBound(arr, 1) 'use split to make Foods/Dog/Treats Pre-Pack into an array, using '\' (chr(92)) as indicator arrTemp = Split(arr(i, lngSplitColumn), Chr(92)) 'every value of arrTemp creates a new row For j = LBound(arrTemp) To UBound(arrTemp) 'loop through all input columns and create the new row For h = LBound(arr, 2) To UBound(arr, 2) If h = lngSplitColumn Then 'setup the value of the splitted column Dim k as long arrSum(h, UBound(arrSum, 2)) = arrTemp(LBound(arrTemp)) for k = LBound(arrTemp)+1 to j arrSum(h, UBound(arrSum, 2)) = arrSum(h, UBound(arrSum, 2)) & "\" & arrTemp(k) 'set Foods Foods/Dog Foods/Dog/Treats Pre-Pack next k Else 'setup the value of any other column arrSum(h, UBound(arrSum, 2)) = arr(i, h) 'set Value of Column h End If Next h ReDim Preserve arrSum(LBound(arr, 1) To UBound(arr, 2), _ LBound(arrSum, 2) To (UBound(arrSum, 2) + 1)) Next j Next i 'clean up last empty row (not realy necessary) ReDim Preserve arrSum(LBound(arr, 1) To UBound(arr, 2), _ LBound(arrSum, 2) To (UBound(arrSum, 2) - 1)) 'setup transposed result array ReDim arrResult(LBound(arrSum, 2) To UBound(arrSum, 2), _ LBound(arrSum, 1) To UBound(arrSum, 1)) 'transpose the array For i = LBound(arrResult, 1) To UBound(arrResult, 1) For j = LBound(arrResult, 2) To UBound(arrResult, 2) arrResult(i, j) = arrSum(j, i) Next j Next i 'specify target range Range(Cells(1, 5), Cells(UBound(arrResult, 1), 4 + UBound(arrResult, 2))) = arrResult End Sub 

Возможно, вам придется адаптировать целевой диапазон.

Cells(1,5) -> E1 являются отправной точкой для вставки

Interesting Posts

Введите двумерный массив в ячейку

Как суммировать значение vlookup диапазона?

пытаясь использовать excel, чтобы подсчитать, сколько раз врач вращается в месяц

создать динамическую строку CountIf для уравнения ячейки VBA

Изменение StartUpPosition Evey UserForm в Project ERROR: объект не поддерживает свойство

Скрипт для проверки PowerPivot и запроса пользователя на установку

Формула Excel – частичное совпадение и значение показа

Удалите лист и заново создайте его с тем же именем листа

Создание PDF-файлов с помощью макроса Excel

Индексирование с именами диапазонов при использовании API расширенных ячеек SpreadSheetGear

Как создать VBA, который создает сводную таблицу только в том случае, если она еще не существует

C # считывает данные из результатов файла excel в ###

В golang, Как я могу выводить новую строку в ячейке в xlsx

Как сохранить файл Excel без предупреждения формата при открытии, используя C # и Excel 2010

Как объединить столбцы с разных листов без дубликатов?

Давайте будем гением компьютера.