marreco
12-08-2012, 02:07 PM
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.
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
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.
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