时间戳

首页 > 解决方案 > Excel VBA中是否有办法在创建数组时使用两个字符函数

excel - Excel VBA中是否有办法在创建数组时使用两个字符函数

问题描述

我目前有一个 VBA 代码，它根据字符串值之间的“/”创建一个数组。

IDnum = Range(Target.Address).Value
IDArray() = Split(IDnum, "/")

我正在尝试编辑它的代码以使其采用“-”或“>”（不一定都只需要其中一个）并在任一侧的任一数字上执行范围以及执行“/”功能我已经有在数组中。IE：输入为“101-105/501-503”，数组为 {101 102 103 104 105 501 502 503}。我一直在想办法做到这一点，并觉得我需要用 if 语句做一个循环。我的想法是否正确，或者是否有一行简单的代码可以实现它。

编辑我的整体代码是：

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
Dim KeyCells As Range, KeyCells2 As Range, R1 As Range
Set R1 = Range("C6:H319")
Set KeyCells = R1
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    Dim IDnum As String, IDArray() As String, Counter As Integer, CIDArray() As Integer, strg As Integer
    Dim i As Integer, Watt As Double, TWatt As Double, TAmp As Double
    TWatt = 0
    'Split apart different fixtures
    IDnum = Range(Target.Address).Value
    IDArray() = Split(IDnum, "/")
    'Apply indexmatch to IDArray
    For Counter = LBound(IDArray) To UBound(IDArray)
        i = (IDArray(Counter) Mod 1000) - (IDArray(Counter) Mod 100)
        Watt = Application.WorksheetFunction.Index(Sheets("Fixture List").Range("L3:L10"), Application.WorksheetFunction.Match(i, Sheets("Fixture List").Range("A3:A10"), 0), 1)
        TWatt = TWatt + Watt
    Next Counter
Set KeyCells2 = Target.Cells
KeyCells2.Offset(1, 12).Value = TWatt
End If
End Sub

标签： excelvba

解决方案

您可以创建一个包含连字符的临时数组，然后从连字符的开头到结尾循环，创建这些数字的字符串，最后将它们全部拆分为一个数组。

    Dim mystr As String
    Dim splitarr As Variant
    Dim temparr As Variant
    Dim tempstr As String
    
    mystr = "101-105/501-503"
    splitarr = Split(mystr, "/")
    
    Dim i As Long
    Dim j As Long
    
    For i = 0 To UBound(splitarr)
        temparr = Split(splitarr(i), "-")
        For j = CLng(temparr(0)) To CLng(temparr(1))
            If Not tempstr = "" Then 'This keeps from having an empty string as index 0.
                tempstr = tempstr & "/" & j
            Else
                tempstr = j
            End If
        Next j
    Next i
            
    splitarr = Split(tempstr, "/")
            
    For i = 0 To UBound(splitarr)
        Debug.Print splitarr(i)
    Next i

我不知道你在用你的数组做什么，所以我只是将结果转储到即时窗口。

推荐阅读