vba For循环更改与范围excel vba有关的公式

For loop to change formula in relation to range excel vba

提问by The Roman Seasonal

Wow, I feel like I'm really close on this one.

哇，我觉得我真的很接近这个。

I have this code in a module:

我在一个模块中有这个代码：

Range("R1400").Formula = "=(ROUND(AC1400, 0))+0.0625"
Range("R1401").Formula = "=(ROUND(AC1401, 0))+0.0625"
Range("R1402").Formula = "=(ROUND(AC1402, 0))+0.0625"
Range("R1403").Formula = "=(ROUND(AC1403, 0))+0.0625"
Range("R1404").Formula = "=(ROUND(AC1404, 0))+0.0625"
Range("R1405").Formula = "=(ROUND(AC1405, 0))+0.0625"
Range("R1406").Formula = "=(ROUND(AC1406, 0))+0.0625"
Range("R1407").Formula = "=(ROUND(AC1407, 0))+0.0625"
Range("R1408").Formula = "=(ROUND(AC1408, 0))+0.0625"
Range("R1409").Formula = "=(ROUND(AC1409, 0))+0.0625"
Range("R1410").Formula = "=(ROUND(AC1410, 0))+0.0625"
Range("R1411").Formula = "=(ROUND(AC1411, 0))+0.0625"
Range("R1412").Formula = "=(ROUND(AC1412, 0))+0.0625"
Range("R1413").Formula = "=(ROUND(AC1413, 0))+0.0625"
Range("R1414").Formula = "=(ROUND(AC1414, 0))+0.0625"
Range("R1415").Formula = "=(ROUND(AC1415, 0))+0.0625"
Range("R1416").Formula = "=(ROUND(AC1416, 0))+0.0625"
Range("R1417").Formula = "=(ROUND(AC1417, 0))+0.0625"
Range("R1418").Formula = "=(ROUND(AC1418, 0))+0.0625"
Range("R1419").Formula = "=(ROUND(AC1419, 0))+0.0625"
Range("R1420").Formula = "=(ROUND(AC1420, 0))+0.0625"
Range("R1421").Formula = "=(ROUND(AC1421, 0))+0.0625"
Range("R1422").Formula = "=(ROUND(AC1422, 0))+0.0625"
Range("R1423").Formula = "=(ROUND(AC1423, 0))+0.0625"
Range("R1424").Formula = "=(ROUND(AC1424, 0))+0.0625"
Range("R1425").Formula = "=(ROUND(AC1425, 0))+0.0625"
Range("R1426").Formula = "=(ROUND(AC1426, 0))+0.0625"
Range("R1427").Formula = "=(ROUND(AC1427, 0))+0.0625"
Range("R1428").Formula = "=(ROUND(AC1428, 0))+0.0625"
Range("R1429").Formula = "=(ROUND(AC1429, 0))+0.0625"

Now, let me be clear, this code works as I'd like it to. It might be obvious that I'm taking a range of numbers in one column, rounding them all to the nearest whole integer, then adding a sixteenth, and representing those values in a different column.

现在，让我说清楚，这段代码按我的意愿工作。很明显，我在一列中取了一系列数字，将它们全部四舍五入到最接近的整数，然后加上十六分之一，并在不同的列中表示这些值。

however

然而

I feel like this could be 'better' executed in less code, and I'd like to become a more efficient programmer, so I try to write it like so:

我觉得这可以用更少的代码“更好地”执行，而且我想成为一个更有效率的程序员，所以我试着这样写：

Dim X As Integer
For X = 1400 To 1429
    Range("R" & X).Formula = "=(ROUND(""AC"" "&X", 0))+0.0625"
Next X

But this (and the variations I've tried) all throw '1004' errors or syntax errors. Most of the changes I've made have had to do with the quotation marks and their placement, and then I've also tried to implement advice from these questions here, here,here,here,and here, but none of them seem to offer quite the solution that I'm looking for, or they don't make sense to me in my inexperience.

但这（以及我尝试过的变体）都会抛出“1004”错误或语法错误。我所做的大部分更改都与引号及其位置有关，然后我还尝试在此处、此处、此处、此处和此处实施这些问题的建议，但似乎没有一个提供我正在寻找的解决方案，否则在我缺乏经验的情况下，它们对我来说没有意义。

It's possible (likely) that my searches haven't contained the proper verbiage I'm looking for, but that's probably because I'm just now taking my first stab at VBA with this :)

有可能（很可能）我的搜索没有包含我正在寻找的正确措辞，但这可能是因为我刚刚开始第一次尝试 VBA :)

Thanks for any help or references. This is my 1st question on StackOverflow, but you folks have definitely taught me darn near everything I know about coding to date.

感谢您的任何帮助或参考。这是我关于 StackOverflow 的第一个问题，但是你们这些人确实教会了我迄今为止我所知道的关于编码的所有知识。

回答by Dmitry Pavliv

As I mentioned in comments above, you don't need loop, you can simply use:

正如我在上面的评论中提到的，你不需要循环，你可以简单地使用：

Range("R1400:R1429").Formula = "=(ROUND(AC1400, 0))+0.0625"

Excel would adjust formula properly for each row, so you would have:

Excel 会为每一行正确调整公式，因此您将拥有：

• in R1400formula =(ROUND(AC1400, 0))+0.0625
• in R1401formula =(ROUND(AC1401, 0))+0.0625
• .....
• in R1429formula =(ROUND(AC1429, 0))+0.0625

• 在R1400公式中=(ROUND(AC1400, 0))+0.0625
• 在R1401公式中=(ROUND(AC1401, 0))+0.0625
• .....
• 在R1429公式中=(ROUND(AC1429, 0))+0.0625

回答by tmoore82

You need to cast the Xvalue in the Rangefunction as a string. And you can pass the value of the referenced cell directly to ROUNDusing the same technique, rather than passing the cell reference. I don't know if that's an improvement, but it was my first instinct.

您需要X将Range函数中的值转换为字符串。并且您可以ROUND使用相同的技术直接传递引用单元格的值，而不是传递单元格引用。我不知道这是否是一种进步，但这是我的第一直觉。

Range("R" & CStr(X)).Formula = "=(ROUND(" & Range("AC" & CStr(X)).Value & ", 0))+0.0625"

As @simoco has pointed out, you don't have to use the loop at all. But if you still wanted to, or run into similar problems in the future, the above should work.

正如@simoco 指出的那样，您根本不必使用循环。但是，如果您仍然想要，或者将来遇到类似的问题，则上述方法应该可行。