Solved: automatically insert formula (WorkSheet_Change)

[marreco]

Hi.
I need to insert formulas automatically.

to enter the values ​​in column "B" cells in columns "C" to "H" will be occupied with formulas, and then will be transformed values ​​format.
[VBA]Sub automatically_insert_formula()
Dim lr As Long
Const sFormula1 As String = "=IF($B2<>"""",VLOOKUP($B2,BASE.DADOS.ANTIGA!$A$2:$G$16980,2,0),"""")"
Const sFormula2 As String = "=IF($B2<>"""",VLOOKUP($B2,BASE.DADOS.ANTIGA!$A$2:$G$16980,3,0),"""")"
Const sFormula3 As String = "=IF($B2<>"""",VLOOKUP($B2,BASE.DADOS.ANTIGA!$A$2:$G$16980,4,0),"""")"
Const sFormula4 As String = "=IF($B2<>"""",VLOOKUP($B2,BASE.DADOS.ANTIGA!$A$2:$G$16980,5,0),"""")"
Const sFormula5 As String = "=IF($B2<>"""",VLOOKUP($B2,BASE.DADOS.ANTIGA!$A$2:$G$16980,6,0),"""")"
Const sFormula6 As String = "=IF($B2<>"""",VLOOKUP($B2,BASE.DADOS.ANTIGA!$A$2:$G$16980,7,0),"""")"



With Sheets("Test")
lr = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
.Range("C2").Resize(lr).Formula = sFormula1
.Range("D2").Resize(lr).Formula = sFormula2
.Range("E2").Resize(lr).Formula = sFormula3
.Range("F2").Resize(lr).Formula = sFormula4
.Range("G2").Resize(lr).Formula = sFormula5
.Range("H2").Resize(lr).Formula = sFormula6
.Range("C2").Resize(lr, 6).Value = .Range("C2").Resize(lr, 6).Value
End With
End Sub
[/VBA]

[p45cal]

try:[VBA]Sub automatically_insert_formula()
Dim lr As Long
With Sheets("Test")
lr = .Cells(.Rows.Count, "B").End(xlUp).Row ' + 1
With .Range("C2:H" & lr)
.Formula = "=IF($B2<>"""",VLOOKUP($B2,BASE.DADOS.ANTIGA!$A$2:$G$16980,COLUMN()-1,0),"""")"
.Value = .Value
End With
End With
End Sub
[/VBA]

[marreco]

Hi
I am very happy to have answered my topic, thanks!

and though I do not know why, when entering a value in "B2" nothing happens in C2: H

Thank you!!!

[p45cal]

and though I do not know why, when entering a value in "B2" nothing happens in C2: H

Your original code contains the line:
.Range("C2").Resize(lr, 6).Value = .Range("C2").Resize(lr, 6).Value
which just replaces any formulae in that range with the result of the formula - like a copy, paste-values operation.
The equivalent in my snippet is:
.Value = .Value
just remove it if you want to keep the formulae in the cells. Then the formulae will recalculate if you change something in column B.

[marreco]

Hi.
what I need is to use it so automatically.
how has this code inside the event (Private Sub Worksheet_Change)?

[p45cal]

what I need is to use it so automatically.
how has this code inside the event (Private Sub Worksheet_Change)?

In the sheet's code-module:[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Set xx = Intersect(Target, Columns(2))
If Not xx Is Nothing Then
For Each are In xx.Areas
With are.Offset(, 1).Resize(, 6)
.FormulaR1C1 = "=IF(RC2<>"""",VLOOKUP(RC2,BASE.DADOS.ANTIGA!R2C1:R16980C7,COLUMN()-1,0),"""")"
.Value = .Value
End With
Next are
End If
End Sub
[/VBA]

[marreco]

Hi.

excellent!!
incredible and amazing results!

thank you very much!!