Excel - VBA - Best way to add formula

Question

I am doing using a very large data set.

I need the best practive (smooth) to add formulas to an specific range.

Maybe using

    Range("A7").Select
    last_row_col_a = Selection.End(xlDown).Row

Instead of entire column

My macro right now:

Sub DIFAL_03()
'
' Passo 1 - Colocar Nomes das colunas

    Range("A7").Select
    last_row_col_a = Selection.End(xlDown).Row
  
    Range("Z6").Value = "CHAVE"
    Range("AA6").Value = "CNPJ.SERIE.NUM_NF"
    Range("AB6").Value = "CHAVE DE PESQUISA"
    Range("AC6").Value = "CFOP"
    Range("AD6").Value = "LISTA CFOP ÚNICOS POR NF"
    Range("AE6").Value = "QTD DE CFOP ÚNICOS POR NF"
    Range("AF6").Value = "CLASSIFICACAO" & Chr(10) & "PRELIMINAR"
    Range("AI6").Value = "COMBINAÇÃO DE CFOP ÚNICOS"
    Range("AJ6").Value = "CLASSIFICAÇÃO PRELIMINAR"
    
' Passo - Colocar as fórmulas do anexo 7
    
    For actual_line = 7 To last_row_col_a
        Range("Z" & actual_line).Value = "=o7"
        Range("AA" & actual_line).Value = "=TEXTJOIN(""."",TRUE,H" & actual_line & ",IFERROR(VALUE(F" & actual_line & "),TRIM(F" & actual_line & "),IFERROR(VALUE(D" & actual_line & "),TRIM(D" & actual_line & "))"
        Range("AB" & actual_line).Value = "=IF(TRIM(Z" & actual_line & ")<> """",Z" & actual_line & ",AA" & actual_line & ")"
        Range("AC" & actual_line).Value = ActiveCell.FormulaR1C1 = "=O" & actual_line
        Range("AD" & actual_line).Value = "=TEXTJOIN("", "", TRUE, (UNIQUE(FILTER(AC" & actual_line & ":AC" & last_row_col_a & ",AB" & actual_line & ":AB" & last_row_col_a & "=AB" & actual_line & "))))"
        Range("AE" & actual_line).Value = "=LEN(TEXTJOIN("", "", TRUE, (UNIQUE(FILTER(AC" & actual_line & ":AC" & last_row_col_a & ",AB" & actual_line & ":AB" & last_row_col_a & "=AB7)))))" & Chr(10) & "/4"
        Range("AI" & actual_line).Value = "=SORT(UNIQUE(AD" & actual_line & ":AD" & last_row_col_a & "))"
        Range("AF" & actual_line).Value = "=VLOOKUP(AD" & actual_line & ";$AI:$AJ;2;FALSO)"
    Next actual_line
    
            
End Sub

I am new on VBA and trying to improve my code.

If someone can give me a clue!

Thanks!!!!!

@ExcelHero and Mr. @chris neilsen ```Range("AA7").Value = "=TEXTJOIN(""."",TRUE,H7,IFERROR(VALUE(F7),TRIM(F7),IFERROR(VALUE(D7),TRIM(D7))" Range("AB7").Value = "=IF(TRIM(Z7)<> """",Z7,AA7)" ```` Is it better to create a for loop for each line? — Andre Nevares, Dec 12 '20 at 20:30

score 1 · Accepted Answer · answered Dec 12 '20 at 20:35

1

Begin by replacing stuff like:

Range("AC7").Select
ActiveCell.FormulaR1C1 = "=RC[-14]"

with:

Range("AC7").FormulaR1C1 = "=RC[-14]"

answered Dec 12 '20 at 20:35

Gary's Student

95,722
10
59
99

Thanks... I have made some changes... but i am facing an error on ```Range("AA" & actual_line).Value = "=TEXTJOIN(""."",TRUE,H" & actual_line & ",IFERROR(VALUE(F" & actual_line & "),TRIM(F" & actual_line & "),IFERROR(VALUE(D" & actual_line & "),TRIM(D" & actual_line & "))"```` – Andre Nevares Dec 12 '20 at 20:52
@AndreNevares Are you trying to put a **value** in the cell or a **formula** ??? – Gary's Student Dec 12 '20 at 21:00
I do not know VBA very well.. I just know a bit of excel and I use those formulas in many jobs. But everytime i have to make the same steps... I was trying to automate this and save some time. – Andre Nevares Dec 12 '20 at 21:04
@AndreNevares You are doing fine! There is always a learning curve. Continue to make corrections. – Gary's Student Dec 12 '20 at 21:07
@AndreNevares Your `Range("AA" & actual_line)` statement is missing 2 **)**, one for each `IFERROR()` – Gary's Student Dec 12 '20 at 21:22

Excel - VBA - Best way to add formula

Maybe using

My macro right now:

1 Answers1