Excel VBA-循环以插入公式 [英] Excel VBA - Loop to insert formulas

问题描述

我遇到了一个充满公式错误的工作表.该项目本身非常重复，因此我决定编写一些简单的VBA代码以节省一些时间.

I've come across a worksheet full of formulas mistakes. The project itself its very repetitive so I decided to write some simple VBA code in order to save me some time.

问题是我的代码无法正常工作.我以为这确实是一个简单的问题，但一直困扰着我的脖子.

The problem is the my code did not work. I thought indeed it was a simple problem but it has been a pain in my neck.

我已经删除了所有重复的代码，以使其更易于理解.

I've cut out all those repetting pieces of the code to make it easier to understand.

Sub corrigirerro()
    Dim r, i As Integer

    For r = 17 To 28
        i = r - 16    
        Worksheets(r).Select    
        Range("D73").ClearContents    
        Range("D73").Formula = "=SEERRO(SOMARPRODUTO('RD - " & i & "'!D266:D268;'RD - " & i & "'!E266:E268)/SOMA('RD - " & i & "'!D266:D268);0)"

推荐答案

您的公式使用的是系统在区域设置中为列表分隔符设置的分号. VBA设置 Range .Formula属性而不用逗号区域设置，但确实有一个范围.FormulaLocal属性区域设置.

Your formula is using the semi-colon that your system has set for the list separator in the regional settings. VBA sets the Range .Formula property with a comma regardless of the regional settings but does have a Range .FormulaLocal property that will use the regional settings.

    Range("D73").Formula = "=SEERRO(SOMARPRODUTO('RD - " & i & "'!D266:D268, 'RD - " & i & "'!E266:E268)/SOMA('RD - " & i & "'!D266:D268), 0)"
    Range("D73").FormulaLocal = "=SEERRO(SOMARPRODUTO('RD - " & i & "'!D266:D268; 'RD - " & i & "'!E266:E268)/SOMA('RD - " & i & "'!D266:D268); 0)"

将公式切换为逗号或使用.FormulaLocal属性.

Switch your formula to commas or use the .FormulaLocal property.

这篇关于Excel VBA-循环以插入公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！