每个循环如何从某一点访问数组？ [英] How to access an array from a certain point every loop?

问题描述

问题详细信息

我的下面的代码将数组中的计算结果存储在数组 funds（）并重复所选范围的进程。到最后，将有一个具有170个值的一维数组。我需要从每个循环的某一点访问数组，以不同的值填充新行。

详细问题

我的核心问题是将该数组打印到由10行×17列组成的工作簿上的范围内。

一旦对于选定范围内的每个单元格循环退出，我设法让它下降一行，但现在它填充具有相同初始数组值的新行！

以下是当前输出：

我尝试了什么？

• 我尝试过Redim，但被示例的长度压倒了。


• 我已经尝试手动复制和粘贴，但觉得这是欺骗...


• 我已经研究了如何通过复制过程删除元素..

总的来说，我相信有一个简单的方法，每个人都知道如何使用！但是是什么？

简而言之...

每个循环删除初始值17，然后将下一个17数组值打印到范围内的新行。重复10次。

代码

  Option Explicit 
公共资金（0到170）作为变量
 
子累积性能（）
 
 Dim selectedrange As Range 
 Dim cell As Range 
 Dim value1 As Double 
 Dim Value2 As Long 
 Dim i，x，d，c As Integer 
 Dim total（0 To 170）As Double 
 
'复制表报告
工作表（数据）。范围（C3：T13）。复制
表（报告）。范围（B39 ）.PasteSpecial 
工作表（数据）。范围（B3：T13）。复制
表格（报告）。范围（A39）PasteSpecial xlPasteValues 
 
'重复9次
c = 39 
对于d = 0到9 
 
c = c + 1 
设置selectedrange =工作表（报告）范围（单元格（c，3），单元格（c，19））
对于每个单元格在selectedrange 
 
 value1 = cell.Value 
 
'单元格在左边当前单元格
 Value2 = cell.Offset（0，-1）.Value 
 
'获得与上个月的差额
 value1 = value1 / Value2  -  1 
 
'将结果+ 1分配给数组
 total（x）= value1 + 1 
 
'如果初始资金槽位为0，则将第一个计算结果存储在该插槽中
如果i = 0，那么
 fund（i）= total（0） -  1 
 ElseIf i> 0然后
'对剩余价值进行计算并存储在基金数组
资金（i）=（资金（i  -  1）+ 1）*总计（i） -  1 
结束如果
 
'MsgBox累积表现：&基金（一）& 单元格地址：& cell.Address 
i = i + 1 
x = x + 1 
 
下一个
 
'从一维数组写入工作表
 Dim目的地作为范围
 Dim j As Integer 
 
设置目标=范围（单元格（c，3），单元格（c，3））'从
开始设置目标=目标。调整大小（1，17） 'UBound函数（基金））
 Destination.Value =基金
 
' MSGBOX 您好 
 
下一D 
 
'一次性命令在这里
范围（C40：S49）。NumberFormat =0.00％
调用portfoliomay 
 
 End Sub 
  

解决方案

目标范围和源数组应具有相同的维度，以便能够由罗森菲尔德评论，正确分配值。这可以通过使用一维数组来重复使用10次，一次只需一行数组（列），或完整目标的二维数组范围（10x17）数组（行，列）。

方法1：1维数组

使用17个值的1维数组，一行一行，一行一行。最初将数组声明为一个动态数组 Dim funds（）... ，所以你可以轻松地重置它。然后在每个的开始设置其零基础长度 ReDim基金（16）... 对于d = 0至9 迭代。其余的代码将保持不变。使用此方法，您的原始目标分配应按预期工作 Destination.Value =资金（或使用等效的较短语句 Cells（c，3）.Resize （1，17）=资金）。

方法＃2：2维数组

您可以将资金申报为基于零的二维数组 Dim fund（9,16）... 。但是，然后没有直接的方式将数据逐行放置。 目的地分配将一次为整个范围 细胞（40,3）.Resize（10,17）=资金 之后您的计算循环结束。您还需要调整资金指令，以指示行资金（d，i）= ... 。这可能是放在数据表中最有效的方式（性能明智），因为将数据放在单元格中相对耗时。

*要做到这一行使用二维数组排列，您必须使用如下所述的解决方法：。

其他调整

您将需要调整总计数组以具有相同的维度和指令资金，或调整 i 和 x 计算。要调整 i 和 x ，并保留总计在的开始添加 i = 0 对于d 迭代，并且仅使用 total（ x）。

Question detail

My code below stores the results from a calculation in array funds() and repeats that process for the selected range. By the end there will be a one dimensional array with 170 values. I need to access the array from a certain point every loop to fill the new row with different values.

Problem in detail

The core problem I am having is printing that array to a range on the workbook which is made up of 10 rows by 17 columns.

I managed to get it to go down a row once the "for each cell in selected range" loop exits, but now it fills the new row with the same initial array values!

Here is the current output:

What have i tried?

• I have tried the Redim but get overwhelmed by the length of examples.
• I have tried manual copy and paste but feel like that is cheating...
• I have researched how to delete elements through the process of copying..

Overall I am sure there is a simple way that everyone knows how to use! but what is it?

In a nutshell...

Every loop remove the initial 17 values, then print the next 17 array values to new row in range. Repeat 10 times.

The code

Option Explicit
Public funds(0 To 170) As Variant

Sub cumulativeperformance()

    Dim selectedrange As Range
    Dim cell As Range
    Dim value1 As Double
    Dim Value2 As Long
    Dim i, x, d, c As Integer
    Dim total(0 To 170) As Double

    'Copy the table to report
    Worksheets("Data").Range("C3:T13").Copy
    Sheets("Report").Range("B39").PasteSpecial
    Worksheets("Data").Range("B3:T13").Copy
    Sheets("Report").Range("A39").PasteSpecial xlPasteValues

    'Repeat 9 times
    c = 39
    For d = 0 To 9

        c = c + 1
        Set selectedrange = Worksheets("Report").Range(Cells(c, 3), Cells(c, 19))
        For Each cell In selectedrange

            value1 = cell.Value

            'get the value of cell to the left of current cell
            Value2 = cell.Offset(0, -1).Value

            'get the difference to previous month
            value1 = value1 / Value2 - 1

            'assign result + 1 to array
            total(x) = value1 + 1

            'If initial fund slot is 0, then store first result of calculation in that slot
            If i = 0 Then
                funds(i) = total(0) - 1
            ElseIf i > 0 Then
                'Do calculation on remaining values and store in fund array
                funds(i) = (funds(i - 1) + 1) * total(i) - 1
            End If

            'MsgBox "cumulative performance: " & funds(I) & " Cell address: " & cell.Address
            i = i + 1
            x = x + 1

        Next

        'Write from one dimensional Array To The worksheet
        Dim Destination As Range
        Dim j As Integer

        Set Destination = Range(Cells(c, 3), Cells(c, 3)) 'starts at
        Set Destination = Destination.Resize(1, 17) 'UBound(funds))
        Destination.Value = funds

        'MsgBox "Hi there"

    Next d

    'one-off commands in here
    Range("C40:S49").NumberFormat = "0.00%"
    Call portfoliomay

End Sub

解决方案

The destination range and the source array should have the same dimensions to be able to assign the values correctly, as commented by Ron Rosenfeld. This is possible by either using a 1-dimension array to reuse 10 times for just one row at a time array(columns), or a 2-dimensions array for the full destination range (10x17) array(rows, columns).

Method #1: 1-dimension array

Use a 1-dimension array of 17 values, for a row by row operation, one row at a time. Initially declare the array as a dynamic array Dim funds() ..., so you'll be able to easily reset it. Then set its zero based length ReDim funds(16) ... at the beginning of each For d = 0 To 9 iteration. The rest of your code will stay the same. With this method your original destination assignment should work as expected Destination.Value = funds (or with an equivalent shorter statement Cells(c, 3).Resize(1, 17) = funds).

Method #2: 2-dimensions array

You can declare funds as a zero based 2-dimensions array Dim funds(9, 16) .... But then there is no straight forward way to put the data in row by row. The destination assignment will be to the whole range at once Cells(40, 3).Resize(10, 17) = funds after your calculation loops end. You will also need to adjust the funds directives to indicate the row funds(d, i) = .... This may be the most efficient way (performance wise) to put in the data in your sheet, as putting data in cells is relatively time consuming.

*To do it row by row with a 2-dimensions array you'll have to use a workaround like the ones described here return an entire row of a multidimensional array in VBA to a one dimensional array.

Other adjustments

You will need to adjust your total array to have the same dimensions and directives as the funds, or adjust i and x calculations. To adjust i and x and leave total as is, add i = 0 at the beginning of your For d iteration, and use only total(x).

这篇关于每个循环如何从某一点访问数组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！