Writing formulas with VBA better methods?

[JumboCactuar]

Hi,
I have a report which is generated with VBA referencing data from another sheet with formulas then turned into values

Example

Range("D2:D1000").FormulaR1C1 = "=R[-1]C[-1]*10"
Range("D2:D1000").value = Range("D2:D1000").value

Which works fine but the problem is if I ever need to modify formulas due to new columns inserted etc... It's a pain having to modify the VBA everytime

Is there a better way? Like readable VBA instead of R1C1 notation would help

Thanks

 

[footoo]

I have a report which is generated with VBA referencing data from another sheet with formulas then turned into values

Your example does not fit this description.

readable VBA instead of R1C1 notation

Instead of the first line of code you posted :
Range("D2:D1000").Formula = "=C1*10"

Or you could replace both lines of code with :
[D2:D1000] = [C2:C1000*10]

 

[footoo]

Amendment:

Or you could replace both lines of code with :
[D2:D1000] = [C1:C999*10]

 

[JumboCactuar]

Amendment:

Or you could replace both lines of code with :
[D2:D1000] = [C1:C999*10]

Thank you this is what I meant. Usually I put formula in the cell, then record macro to get the code.

[D2:D1000] = [C1:C999*10]

Can you do all formula like this? IF statement / fill down etc?

 

[footoo]

Can you do all formula like this? IF statement / fill down etc?

A qualified yes, depending upon what you actually want to do and the actual formulas.
Note : The code posted does not write a formula to the sheet - it places the calculated values directly into D2:D1000

[COLOR=#333333]Usually I put formula in the cell, then record macro to get the code. [/COLOR]

To avoid R1C1 notation, type the formula in the macro like : Range("D2:D1000").Formula = "=C1*10"

 

[mikerickson]

For the OP code, I would use

With Range("D2:D1000")
    .FormulaR1C1 = "=R[-1]C[-1]*10"
    .Value = .Value
End With

That way, there is one location in the VB code to be changed if the cells are different. Its also easier to convert the hard-coded range to a variable.

 

[JumboCactuar]

Thank you both, so I can combine both your solution like:

With Range("E2:E1000")
    .Formula = "=IF(D2>0,D2/60,0)"
    .Value = .Value
End With