在单元格更改中循环一系列单元格，以便在单元格的新值中显示序列中的下一个数字 [英] Loop through a range of cells on cell change to show next number in the sequence as cell's new value

问题描述

我知道如何循环遍历一个范围，

 对于范围内的每个单元格（A1：A5）
如果[条件]然后
结束如果
下一个
  

知道OnChange事件：

  Private Sub Worksheet_Change（ByVal Target As Range）
 Dim KeyCells As Range 
设置KeyCells =范围（A6）
 
如果不是Application.Intersect（KeyCells，Range（Target.Address））Not Not Then然后
调用宏
 End If 
 End Sub 
  

但是，我不确定如何实现以下效果：
我有一个单元格A6，它显示了公式的结果，我有另一个单元格A7，当前的利率在公式中使用，我有多个利率要经过，所以我我们宣称他们是一个范围（Interest_Rates）。我的问题是，一旦A6单元格的值发生变化，我怎么能通过声明的范围来转到下一个数字，并在单元格A7中打印出结果对于范围内的另一个利率，所以我可以打印在表中，然后找到最高的利率。

解决方案

必须做出一些假设。例如，代码假定您的公式在单元格A6中，它实际上如下所示： = SUM（B3 / 100 * A7）。它还假设公式在名为Sheet1的工作表上，它将结果放在表A中的公式结果中。

  Sub tgr（）
 
 Dim wb As Workbook 
 Dim wsData As Worksheet 
 Dim wsDest As Worksheet 
 Dim rInterestCell As Range 
 Dim rDest As范围
 
设置wb = ActiveWorkbook 
设置wsData = wb.Sheets（Sheet1）
设置wsDest = wb.Sheets（公式结果）
 
对于每个rInterestCell In Range（Interest_Range）。单元格
 wsData.Range（A7）。Value = rInterestCell.Value'将兴趣单元格值放在范围A7中，由A6中的公式使用
 wsData.Calculate'根据A7中的新值更新公式结果
设置rDest = wsDest.Cells（wsDest.Rows.Count，A）。End（xlUp）.Offset（1） 
如果rDest.Row< 6然后设置rDest = wsDest.Range（A6）'保证A6是结果的起始单元
 rDest.Value = wsData.Range（A6）。Value'仅将值置于新值目标表中的行
下一个rInterestCell 
 
 End Sub 
  

I understand how to loop through a range,

For Each cell In Range("A1:A5")
    If [condition] Then
    End If
Next

And I'm aware of OnChange event:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("A6")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        Call Macro
    End If
End Sub

However, I am not sure as to how achieve the following effect: I have a cell A6 which shows the 'result' of formula, and I have another cell A7 with current interest rate which is used in the formula, I have multiple interest rates to go through, so I've declared them as a range (Interest_Rates).

My question is, how can I go through declared range to go to the next number once the A6 cell's value has changed, and print it in cell A7 to get the result for another interest rate in the range, so I can print it in the table, and then find the highest rate.

解决方案

Had to make some assumptions. For example, the code assumes that your formula is in cell A6 and that it actually looks like this: =SUM(B3/100*A7). It also assumes that the formula is on a sheet named "Sheet1" and it puts the results in sheet "Formula Results" in column A.

Sub tgr()

    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim wsDest As Worksheet
    Dim rInterestCell As Range
    Dim rDest As Range

    Set wb = ActiveWorkbook
    Set wsData = wb.Sheets("Sheet1")
    Set wsDest = wb.Sheets("Formula Results")

    For Each rInterestCell In Range("Interest_Range").Cells
        wsData.Range("A7").Value = rInterestCell.Value  'Put the interest cell value in range A7, which is used by the formula in A6
        wsData.Calculate    'Update the formula result based on the new value in A7
        Set rDest = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1)
        If rDest.Row < 6 Then Set rDest = wsDest.Range("A6")    'Guarantee that A6 is the starting cell for the results
        rDest.Value = wsData.Range("A6").Value   'Put the value only in a new row in the destination sheet
    Next rInterestCell

End Sub

这篇关于在单元格更改中循环一系列单元格，以便在单元格的新值中显示序列中的下一个数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！