Como repetir o "intervalo de cópia (A1: A5) para o Intervalo B com transformação (coluna para linha)?

Question

Eu tenho um dado no intervalo A

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

e copiei A1 para A5 para Faixa B10, B1 para B5 para Faixa B11, C1 para C5 para Faixa B12 e assim por diante com transformação (de coluna para linha)

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

para um arquivo de dados, os códigos são assim:

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

Como devo repetir este processo em VBa com "para o próximo" para todo o arquivo de dados?

Não consigo descobrir como definir "i".

    

Answer 1

Você quer saber como definir i ? Eu suponho que você quer dizer i , que é comumente usado para nomear um inteiro? Se sim, então estou pensando que você quer usar o VBa, mas tem mais controle?

Isso deve fazer o que você quer, ou dar um bom começo

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

Antes

Apósocódigoserexecutado

    

Answer 2

Vou mostrar-lhe duas maneiras de fazer isso. A primeira é apenas dar o que você pediu - mostrando como incorporar um loop no código que você já possui. A segunda maneira é como eu faria isso de uma maneira mais eficiente.

Método de copiar / colar:
Primeiro, aqui está seu código adaptado. A chave é primeiro identificar quantos dados você tem. Esta informação é armazenada na variável n . A segunda é descobrir como indexar seu loop para que você selecione apenas os blocos de 5 que deseja, sem repetir ou pular nada. Finalmente, você deve descobrir como usar esses mesmos valores de índice para imprimir sua saída na linha apropriada. Então, aqui está o código:

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

Primeiro, n é calculado usando a função da planilha COUNTA . Isso funciona porque seus dados são contíguos e começam na linha 1.

O próximo é o loop for, que vai de 1 a n por 5s - assim, pela primeira iteração i = 1 , pela segunda iteração i = 6 , pela terceira iteração i = 11 e assim por diante.

A chave para selecionar os dados certos é usar o método .Offset do objeto Range . Esse método permite deslocar o intervalo em um número especificado de linhas e colunas. Você deseja deslocar a seleção para ser copiada em 5 linhas cada iteração. Um deslocamento de i - 1 fornece o que você deseja.

Finalmente, você tem que usar .Offset para ajustar a saída abaixo de 1 linha para cada iteração. Como i está avançando em 5s e você não deseja que sua saída seja espaçada em 5 linhas, é necessário reduzir o i em 1/5 como o deslocamento. Aqui (i - 1)/5 + 1 fornece o deslocamento de linha necessário.

Método de processamento de matrizes:
Aqui está outra maneira de abordar o mesmo problema que não usa o método copiar / colar. Ele realiza a transformação nos arrays VBA em vez de trabalhar diretamente na planilha. Isso é muito mais eficiente e, para dados grandes, haverá uma diferença muito notável no tempo de execução.

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

A maioria das diferenças entre as duas formas é notada nos comentários do segundo Sub. A indexação no loop é um pouco mais complicada no segundo, mas a ideia é essencialmente a mesma em ambos.