VBA功能用于上行/下行捕获

发布于 2025-01-17 08:37:29 字数 923 浏览 0 评论 0原文

为我的无知道歉，我是 VBA 新手 - 我确信这是一个简单的问题...... 我正在尝试写一个fn。用于 VBA 中的上/下侧捕获。这就是问题所在：有两列。其中一个以百分比表示基金绩效（我已将其标记为“回报”）。另一个以百分比表示指数表现（标记为“指数”）。两者的长度/行数相同。我需要两者都是变量才能输入到 fn 中。

对于 UpsideCapture fn.，对于所有 no。在索引列 > 0 中，我想在返回列中找到相应的数字（将位于同一行）。一旦我有了这些数字，我就可以将它们复合。
我尝试过使用 Offset，假设返回列是索引列左侧的 15 列，但它不返回任何内容，而且我真的不想依赖它始终相距 15 列（它是任意的）。

非常感谢！

我的垃圾尝试之一如下。非常感谢任何帮助。它实际上只是根据索引列中的值找到正确的相应行的情况...

Function UpsideCapture(returns As Range, index As Range) As Variant
 
Dim n As Integer
Dim m As Integer
Dim i As Integer

n = returns.Rows.Count
m = index.Rows.Count

For i = 1 To m
   If index(i) > 0 Then
     Upsidecap = ((1 + Upsidecap) * (1 + Offset(returns(i), -15))) - 1
   End If
Next

UpsideCapture = Upsidecap
 
End Function

示例

< img src="https://i.sstatic.net/hHMgD.png" alt="test_data">

原文

apologies for my ignorance, I'm brand new to VBA - I'm sure this is a simple problem...
I'm trying to write a fn. for up/down side capture in VBA. This is the problem:
There are two columns. One has fund performance in % (I've labelled 'returns'). The other has index performance in % (labelled 'index'). Both are same length / same number of rows. I need both to be variables to enter to the fn.

For UpsideCapture fn., for all nos. in the index column >0, I want to find the corresponding number in the returns column (which will be on the same row). Once I have those numbers I can compound them.
I've tried using Offset, assuming the returns column is 15 columns to the left of the index column but it doesn't return anything, and I don't really want to rely on it always being 15 columns apart (it arbitrary).

Many thanks!

One of my rubbish attempts is below. Any help is much appreciated. Its really just a case of finding the correct corresponding row based on the value in the index column...

Function UpsideCapture(returns As Range, index As Range) As Variant
 
Dim n As Integer
Dim m As Integer
Dim i As Integer

n = returns.Rows.Count
m = index.Rows.Count

For i = 1 To m
   If index(i) > 0 Then
     Upsidecap = ((1 + Upsidecap) * (1 + Offset(returns(i), -15))) - 1
   End If
Next

UpsideCapture = Upsidecap
 
End Function

example