尝试使用VBA自动拆分Excel数据

Question

除了几周前撰写了一个将数据时间戳添加到生产线上扫描的条形码的函数外，我在Excel VBA编程方面没有任何经验，主要是通过试错。

无论如何，我现在需要帮助的是库存管理即将展开，我们拥有的每个物品都有一个条形码，通常会被扫描到记事本中然后手动导入Excel，并使用“文本分列”功能。我发现了Excel的拆分函数，并希望能得到一点帮助，让它能够处理我的扫描条形码。

数据以以下格式呈现：11111*A153333*11/30/11并附带回车换行符，其中*为分隔符。但我找到的所有示例似乎都没有起到任何作用。

例如，这里是一个在“ ”处分割的示例，但如果我将其更改为*，则什么也不会发生。

Sub splitText()

'splits Text active cell using * char as separator
Dim splitVals As Variant
Dim totalVals As Long

splitVals = Split(ActiveCell.Value, "*")
totalVals = UBound(splitVals)

Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row, ActiveCell.Column + 1 + totalVals)).Value = splitVals
End Sub

这应用于Sheet1代码部分，如果有帮助的话。

真的不可能这么复杂，对吗？

编辑：尝试在VBA中添加Vlookup。

所以我在下面的评论中说过，我现在正在努力将vlookup集成到这个中，但它只返回N/A。

这是我根据下面的链接编写的子程序。

Public Sub vlook(ByRef codeCell As Range)
Dim result As String
Dim source As Worksheet
Dim destination As Worksheet
Set destination = ActiveWorkbook.Sheets("Inventory")
Set source = ActiveWorkbook.Sheets("Descriptions")

result = [Vlookup(destination!(codeCell.Row, D), source!A2:B1397, 2, FALSE)]
End Sub

在工作表更改的For循环后，我正在尝试立即调用它，只是创建了另一个For循环，这应该是嵌套的for循环吗？

- Ian

---

我刚刚运行了您的代码，并针对您指定的测试字符串进行了测试，它可以正常工作 - 将下面三个单元格填充为拆分值。 - Jon Egerton

你把代码放在哪里了？也许这就是我的问题所在？我把代码放到了Sheet1的代码中。当我打开它（Alt+F11）时，我只需将那个小Sub粘贴进去，然后当我用条形码扫描器扫描时，它什么都不做，只是换到下一行。 - Ian

你需要运行代码（例如，作为宏），或者基于逻辑创建某种工作表函数。 - phoog

好的，根据phoog的评论，我将sub更改为Private Sub Worksheet_Change（ByVal Target as Range），但它没有起作用。但如果我选择Worksheet_SelectionChange（），当我选择该列时它就可以工作。到底是怎么回事。 - Ian

Answer 1

如果您希望在输入条形码时自动处理它们，您需要像这样的东西（放在工作表模块中）。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim splitVals As Variant
Dim c As Range, val As String

    For Each c In Target.Cells

        If c.Column = 1 Then 'optional: only process barcodes if in ColA
            val = Trim(c.Value)
            If InStr(val, "*") > 0 Then
                splitVals = Split(val, "*")
                c.Offset(0, 1).Resize( _
                   1, (UBound(splitVals) - LBound(splitVals)) + 1 _
                                       ).Value = splitVals
            End If
        End If 'in ColA

    Next c
End Sub

- Tim Williams

---

我认为需要将“ActiveCell.Value”引用更改为“val”。 - Rachel Hettinger

Answer 2

仅仅将代码添加到工作表后面的VBA中并不能使其被调用。您需要处理worksheet_change事件。以下内容应该会有所帮助：

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim cell As Range
    For Each cell In Target.Cells
        If cell.Column = 1 Then SplitText cell
    Next
    Application.EnableEvents = True
End Sub

Public Sub SplitText(ByRef codeCell As Range)

    'splits Text active cell using * char as separator
    Dim splitVals As Variant
    Dim totalVals As Long

    splitVals = Split(codeCell.Value, "*")
    totalVals = UBound(splitVals)

    Range(Cells(codeCell.Row, codeCell.Column), Cells(codeCell.Row, codeCell.Column + totalVals)).Value = splitVals

End Sub

- Jon Egerton

---

Jon，这正是我想要的，几乎完美。有没有快速的编辑方法，不要在第一列中留下条形码？这样15242A13340111/30/13就变成了15242，但其余部分会继续移到下一列？ - Ian

请参见上面修改后的代码。Statis inProgress的目的在于防止一切循环（因为您正在编辑列1）。 - Jon Egerton

所以从逻辑上讲，他们现在想要添加一个Vlookup描述查找，这是我每周都可以做的，但它在启用vba时根本不想工作。有什么诀窍可以让它工作吗？现在它只返回#N/A，我认为这是因为它正在对实际条形码进行查找而不是剥离后的值。我尝试在这里搜索，并找到了这个，但无法与其他函数协同工作。再次感谢。 - Ian

解决更新无限循环的另一种方法是在“Worksheet_Change”过程的开头添加行“Application.EnableEvents = False”，然后在结尾处重新启用事件。 - Rachel Hettinger

净效应是相同的。我认为最好的方法是防止第一次运行Change事件 - 您只需要确保事件被重新打开即可。FWIW @TimWilliam的版本通过在事件过程中运行代码来避免此问题;唯一的缺点是如果您想从任何其他地方调用SplitText过程。 - Rachel Hettinger