VBA to insert formula in column

[saidhruv]

Hello All,

Can i request for a VBA to auto-populate Formula within specific cells?
Formula and Column address are fixed, however i have around 200 files which has same column and same formula to be applied. Hence if a macro is available, it can be run each time i open the file so as to insert formulas.

Sheet Name: LAB
Column N:3 to N:102
Formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(M3, "⋅ ", "⋅"), "⋅", "⋅ "), "' ", "'"), "' ", "'"), "'", "' ")

Sheet Name: LAB
Column AC:3 to AC:102
Formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(AB3, "⋅ ", "⋅"), "⋅", "⋅ "), "' ", "'"), "' ", "'"), "'", "' ")

Sheet Name: LAB
Column AR:3 to AR:102
Formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(AQ3, "⋅ ", "⋅"), "⋅", "⋅ "), "' ", "'"), "' ", "'"), "'", "' ")

Sheet Name: LAB
Column BG:3 to BG:102
Formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(BF3, "⋅ ", "⋅"), "⋅", "⋅ "), "' ", "'"), "' ", "'"), "'", "' ")

Sheet Name: FINAL (attaching the excel that has the formula that is always required in this sheet).. Note: Please name A1:I1 to A100:I100 as RANGE

regards..

 

[Villalobos]

Hi,

Check out it this.

 

[saidhruv]

Dear Villalobos - i tried using this but it is also updating columns other than N, AC, AR & BH.
The other columns should not be updated since they have their own values/formulas.. Also, FINAL sheet must have the static values available in column A,B,C,H & I and should also have formulas available in D102 to D1001. Please check that.
i sincerely thank you for your efforts. see if this can be resent.


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum ‼

​

 

[Villalobos]

Hi,

This code in the ThisWorkbook put the required formulas on sheet LAB (column N, AC, AR and BG) when you open the file and enable the content. Not update more columns.

Option Explicit
Private Sub Workbook_Open()
    Worksheets("LAB").Range("N3:N102").FormulaR1C1 = _
        "=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RC[-1], ""⋅ "", ""⋅""), ""⋅"", ""⋅ ""), ""'  "", ""'""), ""' "", ""'""), ""'"", ""'  "")"
    Worksheets("LAB").Range("AC3:AC102").FormulaR1C1 = _
        "=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RC[-1], ""⋅ "", ""⋅""), ""⋅"", ""⋅ ""), ""'  "", ""'""), ""' "", ""'""), ""'"", ""'  "")"
    Worksheets("LAB").Range("AR3:AR102").FormulaR1C1 = _
        "=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RC[-1], ""⋅ "", ""⋅""), ""⋅"", ""⋅ ""), ""'  "", ""'""), ""' "", ""'""), ""'"", ""'  "")"
    Worksheets("LAB").Range("BG3:BG102").FormulaR1C1 = _
        "=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RC[-1], ""⋅ "", ""⋅""), ""⋅"", ""⋅ ""), ""'  "", ""'""), ""' "", ""'""), ""'"", ""'  "")"
End Sub

Could you explain deeper the issues (if any) with the FINAL sheet?

 

[saidhruv]

Dear Villalobos - i am so sorry, i could not understand 'when you enable the content' part. how will the above formulas be pasted in the sheets that i open.. can this formula run in specific sheets i want to run it?

basically can you pls teach me how to run your code in the files i want it to run?