时间戳

首页 > 解决方案 > 如何比较 Excel VBA 单元格中的上一个、当前和下一个元素？

excel - 如何比较 Excel VBA 单元格中的上一个、当前和下一个元素？

问题描述

我是 VBA 新手

我在 Java 中有以下方法来查找数组中的高低元素，但我不知道如何在 VBA Excel 上做同样的事情。问题在于翻译 IF 以比较数组中的前一个、当前和下一个元素{if (arr[i] > arr[i + 1] && arr[i] > arr[i - 1])}，在 Excel 的情况下，是如何在 ForEach 运行时在一列单元格中查找上一个、当前和下一个元素。

public static ArrayList<Integer> sequence(int[] arr) {
    System.out.println(" HIGHEST HIGH " + arr.length);
    ArrayList<Integer> relHigh = new ArrayList<Integer>();
    ArrayList<Integer> relLow = new ArrayList<Integer>();
    ArrayList<Integer> hestHigh = new ArrayList<Integer>();
    ArrayList<Integer> lestLow = new ArrayList<Integer>();
    int highest = -9999999;
    int lowest = 9999999;
    for (int i = 1; i < arr.length - 2; i++) {
        // System.out.println(" I: " + i + " : " + arr[i]);
        if (arr[i] > arr[i + 1] && arr[i] > arr[i - 1]) {
            relHigh.add(arr[i]);
            System.out.println("RelHigh I: " + i + " : " + arr[i]);
            if (arr[i] > highest) {
                hestHigh.add(arr[i]);
                highest = arr[i];
                System.out.println("HH I: " + i + " : " + arr[i]);
            }
        }
        if (arr[i] < arr[i + 1] && arr[i] < arr[i - 1]) {
            relLow.add(arr[i]);
            System.out.println("RelLow I: " + i + " : " + arr[i]);
            if (arr[i] < lowest) {
                lestLow.add(arr[i]);
                lowest = arr[i];
                System.out.println("LL I: " + i + " : " + arr[i]);
            }
        }
    }
    return relHigh;
}

在此处输入图像描述

Sub MaxMin()

Dim myCount As Integer
Dim i As Integer
Dim relHigh As Double
Dim relLow As Double
Dim hestHigh As Double
Dim lestLow As Double
Dim highest As Double
Dim lowest As Double

Dim Cellx As Range
Dim MyRange As Range
Set MyRange = ThisWorkbook.Sheets("Hoja1").Range("A:A").CurrentRegion

Dim previous As Double
Dim current As Double
Dim next As Double

Dim rCell As Range
Dim rRng As Range

Set rRng = Sheets("Hoja1").Range("A1:A15")

For Each rCell In rRng.Cells
    Debug.Print rCell.Address, rCell.Value
    **// Here is where I get lost**
    if (arr[i] > arr[i + 1] && arr[i] > arr[i - 1]) {
Next rCell

End Sub

标签： excelvba

解决方案

您可以使用Transpose将一列数据分配给数组。您不能添加到数组（无需重新确定尺寸），但可以添加到Collection。

Function sequence(ByRef rng As Range) As Variant

    ' assign range to array
    Dim arr As Variant
    arr = WorksheetFunction.Transpose(rng.Value2)
  
    Dim relHigh As New Collection, relLow As New Collection
    Dim highestHigh As New Collection, lowestLow As New Collection
    Dim highest As Long, lowest As Long, i As Long
    Dim highestRow As Long, lowestRow As Long

    highest = -9999999
    lowest = 9999999
    ' clear formatting
    rng.Cells.ClearFormats
    rng.Columns(2).Clear

    For i = 2 To UBound(arr) - 1
      
        If (arr(i) > arr(i + 1)) And (arr(i) > arr(i - 1)) Then
            relHigh.Add arr(i)
            'System.out.println("RelHigh I: " + i + " : " + arr[i]);
            Debug.Print "RelHigh I:" & i & " : " & arr(i)
            rng.Cells(i, 1).Font.Color = RGB(0, 200, 0)
            rng.Cells(i, 1).Font.Bold = True
            rng.Cells(i, 2) = "relHigh"

            If (arr(i) > highest) Then
                highestHigh.Add arr(i)
                highest = arr(i)
                highestRow = i
                'System.out.println("HH I: " + i + " : " + arr[i]);
                Debug.Print "HH I:" & i & " : " & arr(i)
            End If
        End If

        If (arr(i) < arr(i + 1)) And (arr(i) < arr(i - 1)) Then
            relLow.Add arr(i)
            rng.Cells(i, 1).Font.Color = vbRed
            'System.out.println("RelLow I: " + i + " : " + arr[i]);
            Debug.Print "RelLow I:" & i & " : " & arr(i)
            rng.Cells(i, 1).Font.Color = RGB(200, 0, 0)
            rng.Cells(i, 1).Font.Bold = True
            rng.Cells(i, 2) = "relLow"

            If (arr(i) < lowest) Then
                lowestLow.Add arr(i)
                lowest = arr(i)
                lowestRow = 1
                'System.out.println("HH I: " + i + " : " + arr[i]);
                Debug.Print "LL I:" & i & " : " & arr(i)
            End If
        End If
        ' store latest
        If (arr(i) = highest) Then highestRow = i
        If (arr(i) = lowest) Then lowestRow = i

    Next
    'highest / lowest
    rng.Cells(highestRow, 2) = "hestHigh"
    rng.Cells(lowestRow, 2) = "lestLow"

    ' return relHigh collection as an array
    Dim arrOut() As Integer
    ReDim arrOut(1 To relHigh.Count, 1 To 1)
    For i = 1 To relHigh.Count
        Debug.Print i, relHigh(i)
        arrOut(i, 1) = relHigh(i)
     Next
     sequence = arrOut

End Function

推荐阅读