Como usar for-loop em uma macro do excel

Question

Sou completamente novo no Excel, mas quero criar uma macro na qual possa preencher duas colunas e o número inicial e final das linhas, e assim será feito:

=(A1*B1+A2*B2+A3*B3...)/((SUM of column B)/N))

em que N é o número de linhas.

Como eu faria isso no excel?

    

Answer 1

Em primeiro lugar, as coisas serão muito mais fáceis se você usar os endereços de células padrão!

Geralmente, as células são referidas por seus endereços "A1", mas em VBA (Visual Basic for Applications), elas também podem ser referenciadas usando coordenadas [Row, Column]. Por exemplo, Cell(1, 2) seria "B1".

Você pode calcular o valor com Fórmulas ou com o VBA personalizado (macros). Fórmulas são frequentemente a alternativa mais fácil - mas definitivamente há méritos em usar VBA em alguns casos.

Usando fórmulas

Aqui está o layout dos dados da amostra:

AcolunaAeBsãoambaspreenchidascomosnúmerosaseremmultiplicadosjuntos.

Acélula"C2" contém a fórmula usada para calcular o valor.

=(SUMPRODUCT(A:A;B:B)/SUM(B:B))/(COUNTA(B:B)-1)

• SUMPRODUCT multiplica os valores das colunas A e B para cada linha e os soma.
• SUM soma todos os números encontrados na coluna B .
• COUNTA calcula o número de células não vazias na coluna B (temos que deduzir o cabeçalho).

Procure as funções na página de suporte para mais informações.

Usando o VBA

Você pode conseguir a mesma coisa (e muito mais!) usando VBA .

Abra o VBE (Editor do Visual Basic) com Alt + F11 ou a partir do Guia do desenvolvedor .

Crie um novo módulo de código padrão.

Coleocódigoabaixonomódulo.Vocêpodeajustá-loparamelhoratenderàssuasnecessidades.

OptionExplicitSubTest()DimtargetSheetAsWorksheetDimvalueColumnAAsLongDimvalueColumnBAsLongDimheaderSizeAsLongDimlastRowAsLongDimcurrentRowAsLongDimvalueAsDouble'''---Config---'''SettargetSheet=ThisWorkbook.Sheets(1)valueColumnA=1valueColumnB=2headerSize=1lastRow=targetSheet.Cells(targetSheet.Rows.Count,"A").End(xlUp).Row

    '''---Logic---'''
    'Multiply the columns for each row and sum them up
    For currentRow = headerSize + 1 To lastRow
        value = value _
            + targetSheet.Cells(currentRow, valueColumnA).value _
            * targetSheet.Cells(currentRow, valueColumnB).value
    Next

    'Divide by sum of numbers found in valueColumnB
    value = value / Application.WorksheetFunction.Sum( _
        targetSheet.Columns(valueColumnB))

    'Divide by number of rows
    value = value / (lastRow - headerSize)

    Debug.Print value
End Sub

StackOverflow tem muitas boas perguntas e respostas sobre VBA .