VBA:替换数组元素

Question

编辑:根据评论，我提供了关于代码的更多细节。

守则的理念是：

在范围B6:E6中存储了字符串(例如B6 =“实际销售”，C6 = "SOP11 (2015年)“，D6 = "SOP12 (2015年)”，E6 = "SOP10 (2015年)“)。

如果字符串不是“实际销售”，则使用“中间”函数计算整数。

完成后，计算出的整数将使用数组中的BubbleSort进行排序。

之后，我想将排序整数 (SOP_key_B6、SOP_key_C6、SOP_key_D6、SOP_key_E6)与原始字符串(cell_b6、cell_c6、cell_d6、cell_e6)链接起来。换句话说，SOP_key_B6和cell_b6之间有一对一的通信，等等)

我想要这样做，因为我需要向范围L30:O30 输入基于排序整数的字符串排序数组。

我希望这能让这个想法变得清晰，因为它并不复杂，但是方法本身&代码让它有点令人沮丧(可能是因为我还在学习VB编码)。

下面是代码：

Sub Worksheet_Delta_Update()

'Variables
Dim wb As Workbook, ws_wk_dlt As Worksheet, ws_dash As Worksheet, cell_B6 As  Variant, _
cell_C6 As Variant, cell_D6  As Variant, cell_E6 As Variant, SOP_key_B6 As Variant, _
SOP_key_C6 As Variant, SOP_key_D6 As Variant, SOP_key_E6 As Variant

'Referencing
Set wb = ThisWorkbook
Set ws_wk_dlt = wb.Worksheets("t")
Set ws_dash = wb.Worksheets("x")

'Values from pivot stored
cell_B6 = ws_wk_dlt.Range("B6").Value
cell_C6 = ws_wk_dlt.Range("C6").Value
cell_D6 = ws_wk_dlt.Range("D6").Value
cell_E6 = ws_wk_dlt.Range("E6").Value

'If len certain amount of characters then do option 1, or option 2
If cell_B6 <> "" Then
    If Len(cell_B6) = 12 And cell_B6 <> "Actual Sales" Then
            SOP_key_B6 = CInt(Mid(cell_B6, 4, 2)) + CInt(Mid(cell_B6, 8, 4))
    ElseIf Len(cell_B6) = 11 And cell_B6 <> "Actual Sales" Then
        SOP_key_B6 = CInt(Mid(cell_B6, 4, 2)) + CInt(Mid(cell_B6, 7, 4))
    End If
End If

If cell_C6 <> "" Then
    If Len(cell_C6) = 12 And cell_C6 <> "Actual Sales" Then
            SOP_key_C6 = CInt(Mid(cell_C6, 4, 2)) + CInt(Mid(cell_C6, 8, 4))
    ElseIf Len(cell_C6) = 11 And cell_C6 <> "Actual Sales" Then
        SOP_key_C6 = CInt(Mid(cell_C6, 4, 2)) + CInt(Mid(cell_C6, 7, 4))
    End If
End If

If cell_D6 <> "" Then
    If Len(cell_D6) = 12 And cell_D6 <> "Actual Sales" Then
            SOP_key_D6 = CInt(Mid(cell_D6, 4, 2)) + CInt(Mid(cell_D6, 8, 4))
    ElseIf Len(cell_D6) = 11 And cell_D6 <> "Actual Sales" Then
        SOP_key_D6 = CInt(Mid(cell_D6, 4, 2)) + CInt(Mid(cell_D6, 7, 4))
    End If
End If

If cell_E6 <> "" Then
    If Len(cell_E6) = 12 And cell_E6 <> "Actual Sales" Then
            SOP_key_E6 = CInt(Mid(cell_E6, 4, 2)) + CInt(Mid(cell_E6, 8, 4))
    ElseIf Len(cell_E6) = 11 And cell_E6 <> "Actual Sales" Then
        SOP_key_E6 = CInt(Mid(cell_E6, 4, 2)) + CInt(Mid(cell_E6, 7, 4))
    End If
End If

'Finding the Actual Sales and putting into L30
If cell_B6 = "Actual Sales" Then
    ws_dash.Range("L31").Value = cell_B6
ElseIf cell_C6 = "Actual Sales" Then
    ws_dash.Range("L31").Value = cell_C6
ElseIf cell_D6 = "Actual Sales" Then
    ws_dash.Range("L31").Value = cell_D6
ElseIf cell_E6 = "Actual Sales" Then
    ws_dash.Range("L31").Value = cell_E6
End If

'BubbleSort in Descending order
Dim ArrayToSort(0 To 4) As Variant

ArrayToSort(0) = SOP_key_B6
ArrayToSort(1) = SOP_key_C6
ArrayToSort(2) = SOP_key_D6
ArrayToSort(3) = SOP_key_E6

'Moving upwards because of -1
For j = UBound(ArrayToSort) - 1 To LBound(ArrayToSort) Step -1

  'Starting at lowest
    For i = LBound(ArrayToSort) To j
      If ArrayToSort(i) > ArrayToSort(i + 1) Then
      vTemp = ArrayToSort(i)
      ArrayToSort(i) = ArrayToSort(i + 1)
      ArrayToSort(i + 1) = vTemp
      End If
    Next i
Next j

'Put sorted array into the range
'But how to put the values linked to integers?
'E.g. SOP_key_B6 = cell_B6 
 ws_dash.Range("L30:O30").Value = ArrayToSort

 End Sub

最可能的解决方案是用正确的数组元素(即SOP_key_B6 = cell_B6等)替换数组元素。

Answer 1

您的代码在某些地方臃肿，例如：

Dim ArrayToSort(0 To 4) As Variant

ArrayToSort(0) = SOP_key_B6
ArrayToSort(1) = SOP_key_C6
ArrayToSort(2) = SOP_key_D6
ArrayToSort(3) = SOP_key_E6

可代之以

Dim ArrayToSort As Variant 'note lack of ()
ArrayToSort = Array(SOP_key_B6, SOP_key_C6, SOP_key_D6, SOP_key_E6)

就您的问题而言，您似乎需要使用一个集合。假设SOP-key_值和cell_值之间存在一对一的对应关系(否则，将它们称为“键”是有误导性的)，您可以这样做：

Dim C As New Collection
C.Add cell_B6, CStr(SOP_key_B6)
C.Add cell_C6, CStr(SOP_key_C6)
C.Add cell_D6, CStr(SOP_key_D6)
C.Add cell_E6, CStr(SOP_key_E6)

然后，在对ArrayToSort进行排序之后，有一个循环，如：

For i = 0 to 3
    Range("L30").Offset(0,i).Value = C(CStr(ArrayToSort(i)))
Next i

我想这就是你想要的--但是代码看起来是复杂的，所以稍微简化一下可能不是个坏主意。

编辑：

通过添加SOP11(2015)与SOP10(2016)不同，但是11+2015 = 10 + 2016 (两者都等于2026年)，可以得到重复的键。相反--并列: 112015不是102016。

此外，将键创建拆分为自己的函数也是有意义的(因此您不会重复相同的代码4次)：

Function ExtractKey(s As Variant) As Long
    Dim v As Variant, n As Long
    v = Trim(s)
    If v Like "*(*)" Then
        n = Len(v)
        v = Mid(v, n - 7, 7)
        v = Replace(v, "(", "")
        ExtractKey = CLng(v)
    Else
        ExtractKey = 0
    End If
End Function

请注意，返回类型是Long - Integer变量太容易溢出，在VBA中不起作用。

然后--像这样的东西应该管用：

Sub Worksheet_Delta_Update()
    Dim SourceRange As Range, TargetRange As Range
    Dim i As Long, j As Long, minKey As Long, minAt As Long
    Dim v As Variant
    Dim C As New Collection

    Set SourceRange = Worksheets("t").Range("B6:E6")
    Set TargetRange = Worksheets("t").Range("L30:O30")

    For i = 1 To 4
        v = SourceRange.Cells(1, i).Value
        C.Add Array(ExtractKey(v), v)
    Next i

    'transfer data
    For i = 1 To 4
        minAt = -1
        For j = 1 To C.Count
            If minAt = -1 Or C(j)(0) < minKey Then
                minKey = C(j)(0)
                minAt = j
            End If
        Next j
        TargetRange.Cells(1, i).Value = C(minAt)(1)
        C.Remove minAt
    Next i
End Sub

Answer 2

对Type mismatch error进行以下修改：

Function ExtractKey(s As Variant) As Long
   Dim v As Variant, n As Long
   v = Trim(s) 'remove spaces leave only spaces between words
     If v Like "*(*)" Then 'if it's SOPXX (YYYY) then
       n = Len(v) 'find number of the characters
         If n = 11 Then
           v = Mid(v, n - 7, 7) 'find the number of SOP + year in bracket
         ElseIf n = 12 Then
           v = Mid(v, n - 8, 8)
         End If
        v = Replace(v, "(", "") 'replace the brackets with nothing
        v = Replace(v, " ", "")
        ExtractKey = CLng(v) 'error WAS here
      Else
        ExtractKey = 0
      End If
End Function

编辑：又增加了几行

 If n = 11 Then
         v = Right(v, 4) + Left(v, 1)
    ElseIf n = 12 Then
        v = Right(v, 4) + Left(v, 2)
    End If

上述开关年份和数目(例如，SOP12 (2015年)= 122015和开关201512之后)。这是因为SOP12 (2014年)被放在了 SOP10 (2015年)之后，尽管它应该提前到2014年。现在像魅力一样工作:)