在VBA Excel中使用循环填充单元格 [英] Filling cells with loop in VBA Excel

问题描述

我正在尝试用这样的for循环填充单元格:

I'm trying to fill cells with a for loop like this:

 For i = 1 To Target 
    Range("C" & i & ":C" & i ).Formula = "='Sheet1'!A" & i & "/" & "'Sheet2'!B" & i" 
 Next i

我想在编辑栏中看到它:

And I want to see that in the formula bar:

='Sheet1'！A1&"/"&'Sheet2'！B1

='Sheet1'!A1 & "/" & 'Sheet2'!B1

='Sheet1'！A2&"/"&"Sheet2"！B2

='Sheet1'!A2 & "/" & 'Sheet2'!B2

...

很遗憾，它无法正常工作.如果我只尝试这样的第一部分:

Unfortunately it's not working. If I try only the first part like that:

 For i = 1 To Target 
    Range("C" & i & ":C" & i ).Formula = "='Sheet1'!A" & i 
 Next i

此代码可以达到很好的效果，但这对我来说还不够:

This code results this fine, but this is not enough for me:

='Sheet1'！A1

='Sheet1'!A1

='Sheet1'！B1

='Sheet1'!B1

...

我的第一个代码有什么问题?

What is wrong with my frist code?

推荐答案

如果您在串联字符串中使用双引号时遇到麻烦，我建议您删除尽可能多的双引号.可以使用 Chr函数通过其ASCII码编号来引用单个字符../字符是47.

If you are having trouble with double quotes in a concatenated string, I sugest you remove as many as you can. Single characters can be referred to by their ASCII code number with the Chr function. The / character is 47.

Dim i As Long, target As Long

With ActiveSheet
    target = .Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To target
       .Range("C" & i).Formula = "='Sheet1'!A" & i & Chr(47) & "'Sheet2'!B" & i
    Next i
End With

如果公式构造正确，则不必增加范围.可以使用xlA1或xlR1C1参考样式(请参见

You do not have to increment through a range is the formula is constructed properly. Either an xlA1 or xlR1C1 reference style can be used (see xlReferenceStyle enumeration).

Dim i As Long, target As Long

With ActiveSheet
    target = .Cells(Rows.Count, "A").End(xlUp).Row
    'xlA1 style
    .Range("C1:C" & target).Formula = "='Sheet1'!A1/'Sheet2'!B1"
    'xlR1C1 Style
    .Range("C1:C" & target).FormulaR1C1 = "='Sheet1'!RC1/'Sheet2'!RC2"
End With

请注意，xlR1C1样式需要 Range.FormulaR1C1 而不是 Range.Formula .

Note that an xlR1C1 style requires the Range.FormulaR1C1 instead of the Range.Formula.

这篇关于在VBA Excel中使用循环填充单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！