时间戳

首页 > 解决方案 > 如何在我的 for/next 循环中添加这些值和文本字符串？

vba - 如何在我的 for/next 循环中添加这些值和文本字符串？

问题描述

我有一系列数字，它们会根据自己的单独选项卡更新到主表。我在其中输入一组数字，rngB它们根据数组中的乘数根据 for/next 循环的维度进行填充multplr。

一切正常，但我也试图(x,40)用这个填充一个范围：wsA.Cells(x, 40).Formula = "=""TSR: ""&AX6&"" - ""&AY6&"" - ""&AZ6&"" USD Annual"

环形：

        For x = 6 To FinalRow
            wsA.Cells(x, 40).Formula = "=""TSR: ""&AX6&"" - ""&AY6&"" - ""&AZ6&"" USD Annual"
        Next x

它填充公式，但不会像它应该的那样自动填充，它只是重复保存的东西Cells(6,40)

我知道有自动填充/目标方法，但是我之前的 for/next 公式循环可以正常工作，所以我很困惑这里发生了什么。

脚本的其余部分：

Sub UpdateTSRS()

Dim wbk As Workbook
Dim wsA As Worksheet, wsB As Worksheet
Dim rngA As Range, rngB As Range, rngC As Range
Dim rIterator As Range, c As Range, spread As Range
Dim fndRow As Long, i As Long, j As Long, x As Long
Dim multplr As Variant
Dim FinalRow
multplr = Array(1, 1.1, 1.15, 1.2, 1.3)

Set wbk = ThisWorkbook
Set wsA = wbk.Sheets("Annual")
Set wsB = wbk.Sheets("New Annual")
Set rngA = wsA.Range(wsA.Range("E6"), wsA.Range("E6").End(xlDown))
Set rngB = wsB.Range(wsB.Range("A2"), wsB.Range("A2").End(xlDown))
Set rngC = wsA.Range(wsA.Range("AW6"), wsA.Range("AW6").End(xlDown))

FinalRow = wsA.Cells(Rows.Count, 49).End(xlUp).Row

For Each rIterator In rngB
        On Error Resume Next
        fndRow = Application.Match(rIterator.Value, rngA, 0) + _
            rngA.Range("E1").Row - 1
        If Err.Number <> 0 Then
        Else
        For i = 0 To 4
            For j = 3 To 9
                If j <> 6 Then
                Set c = wsA.Cells(fndRow + i, j + 43)
                c.Interior.Color = VBA.RGB(255, 255, 0)
                wsA.Cells(fndRow + i, j + 43).Value = rIterator.Offset(, j - 1).Value * multplr(i)
            End If
            Next j
        Next i
        End If
        Err.Clear
    Next rIterator



        For x = 6 To FinalRow
            wsA.Cells(x, 49).FormulaR1C1 = "=RC[-1]-RC[-3]"
        Next x

        For x = 6 To FinalRow
            wsA.Cells(x, 40).Formula = "=""TSR: ""&AX6&"" - ""&AY6&"" - ""&AZ6&"" USD Annual"
        Next x
End Sub

标签： vba

解决方案

不需要循环：

wsA.Range(wsA.Cells(6, 40),wsA.Cells(FinalRow, 40)).Formula = "=""TSR: ""&AX6&"" - ""&AY6&"" - ""&AZ6&"" USD Annual"

与其他公式相同：

wsA.Range(wsA.Cells(6, 49),wsA.Cells(FinalRow, 49)).FormulaR1C1 = "=RC[-1]-RC[-3]"

推荐阅读