Как повторить «копирование диапазона (A1:A5) в диапазон B с преобразованием (от столбца к строке)?

Question

У меня есть данные в диапазоне А

1 a1
2 a2
3 a3
4 a4
5 a5
6 b1
7 b2
8 b3
9 b4
10 b5

и я скопировал A1 в A5 в диапазон B10, B1 в B5 в диапазон B11, C1 в C5 в диапазон B12 и т. д. с преобразованием (из столбца в строку)

a1 a2 a3 a4 a5
b1 b2 b3 b4 b5
n1 n2 n3 n4 n5

для одного файла данных коды выглядят так:

Sub XX
    Range("A1:A5").Select
    Selection.Copy
    Range("B10").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End Sub

Как мне повторить этот процесс в VBa с "для следующего" для всего файла данных?

Я не могу понять, как определить "я".

Answer 1

Вы хотите знать, как определить i? Я предполагаю, что вы имеете в виду i который обычно используется, чтобы назвать целое число? Если так, то я думаю, что вы хотите использовать VBa, но иметь больше контроля?

Это должно делать то, что вы хотите, или дать вам хорошее начало

Option Explicit
Sub DoTheThingThePersonWantsWithExcel()

Dim resultsRow As Integer
resultsRow = 10 ' this is where the results will be shown

Dim resultsColumn As Integer
resultsColumn = 66

Dim currentRow As Integer
currentRow = 1

Dim previousCharacter As String
previousCharacter = ""

Do While (Range("A" & currentRow).Value <> "")

    Dim currentValue As String
    currentValue = Range("A" & currentRow).Value

    Dim currentCharacter  As String
    currentCharacter = Left(currentValue, 1)

    If previousCharacter = "" Then
        previousCharacter = currentCharacter
    End If

      If (currentCharacter <> previousCharacter) Then
        previousCharacter = currentCharacter
        resultsColumn = 66
        resultsRow = resultsRow + 1
      End If

      If (currentCharacter = previousCharacter) Then
        Range(Chr(resultsColumn) & resultsRow).Value = currentValue
        resultsColumn = resultsColumn + 1
      End If

    currentRow = currentRow + 1
Loop

End Sub

До

После запуска кода

Answer 2

Я собираюсь показать вам два способа сделать это. Первый - просто дать вам то, о чем вы просили - показать, как включить цикл for в код, который у вас уже есть. Второй способ - как бы я делал это более эффективно.

Метод копирования / вставки:
Во-первых, вот ваш адаптированный код. Ключ должен сначала определить, сколько данных у вас есть. Эта информация хранится в переменной n . Во-вторых, нужно выяснить, как индексировать цикл for, чтобы вы выбирали только те куски, которые вам нужны, без повторения или пропуска чего-либо. Наконец, вы должны выяснить, как использовать те же самые значения индекса, чтобы вывести ваш вывод в соответствующую строку. Итак, вот код:

Sub XX()
    Dim n As Long
    n = [COUNTA(A:A)]
    For i = 1 To n Step 5
        Range("A1:A5").Offset(i - 1, 0).Select
        Selection.Copy
        Range("B10").Offset((i - 1) / 5 + 1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
    Next i
End Sub

Во-первых, n рассчитывается с использованием функции листа COUNTA . Это работает, потому что ваши данные являются смежными и начинаются со строки 1.

Далее следует цикл for, который идет от 1 до n на 5 с - так, для первой итерации i = 1 , для второй итерации i = 6 , для третьей итерации i = 11 и т.д.

Ключом к выбору правильных данных является использование .Offset объекта Range . Этот метод позволяет сместить диапазон на указанное количество строк и столбцов. Вы хотите сдвинуть выделенную область на 5 строк на каждую итерацию. Смещение i - 1 дает вам то, что вы хотите.

Наконец, вы должны использовать .Offset для регулировки вывода на 1 строку для каждой итерации. Поскольку i шагаю в 5 с, и вы не хотите, чтобы ваш вывод был разнесен на 5 строк, вы должны уменьшить i на 1/5 в качестве смещения. Здесь (i - 1)/5 + 1 дает вам необходимое смещение строки.

Метод обработки массива:
Вот еще один способ решения той же проблемы, который не использует метод копирования / вставки. Он выполняет преобразование для массивов VBA вместо того, чтобы работать непосредственно с рабочим листом. Это гораздо более эффективно, и для больших данных во время выполнения будет очень заметная разница.

Sub Reorganize()
Dim colData() As Variant, i As Long
Dim outData() As Variant, j As Long
'Store all data from column in array for fast processing
colData = Range("A1").Resize([COUNTA(A:A)], 1).Value
'Size output array to match data
ReDim outData(1 To Int((UBound(colData, 1) - 1) / 5) + 1, 1 To 5) As Variant
'Loop through array and print values to corresponding indices in output array
For i = LBound(colData, 1) To UBound(colData, 1)
    outData(Int((i - 1) / 5) + 1, ((i - 1) Mod 5) + 1) = colData(i, 1)
Next i
'Print reorganized data to sheet
Range("B10").Resize(UBound(outData, 1), UBound(outData, 2)).Value = outData
End Sub

Большая часть различий между этими двумя способами отмечена в комментариях ко второму подпункту. Индексирование в цикле немного сложнее в последнем, но идея по сути одинакова в обоих.