VBA/EXCEL:从一个包含多个逗号值的单元格中提取数字 [英] VBA/EXCEL: extract numbers from one cell that contained multiple values with comma
问题描述
http://i.stack.imgur.com/53tz0.jpg 是图片.
您好,我想修改此代码,因此它可以理解多个值.我最好的是条形码扫描仪,我是vba
的新手,但我真的很喜欢.这是我做的一个有趣的项目,请帮助我.谢谢大家.
Hello I would like to modify this code, so it understands multiple values. the best I came was the barcode scanner, I'm a novice in vba
but I really enjoy it. this is a fun project that I do so please help me. Thanks guys.
Private Sub Worksheet_Change(ByVal Target As Range)
Const SCAN_CELL As String = "A1"
Const RANGE_BC As String = "A5:A500"
Dim val, f As Range, rngCodes As Range
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range(SCAN_CELL)) Is Nothing Then Exit Sub
val = Trim(Target.Value)
If Len(val) = 0 Then Exit Sub
Set rngCodes = Me.Range(RANGE_BC)
Set f = rngCodes.Find(val, , xlValues, xlWhole)
If Not f Is Nothing Then
With f.Offset(0, 2)
.Value = .Value + 1
End With
Else
Set f = rngCodes.Cells(rngCodes.Cells.Count).End(xlUp).Offset(1, 0)
f.Value = val
f.Offset(0, 1).Value = "enter description"
f.Offset(0, 2).Value = 1
End If
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
Target.Select
End Sub
推荐答案
要获取存储在一个单元格中的多个值,这些值用逗号或任何特殊字符分隔,可以使用vba函数Split.
To get multiple values stored in one cell that are separated by a comma or any special character you can use the vba function Split.
例如,假设我们有一个单元格C5,该单元格的以下四个数字之间用逗号隔开:
For example, let's say we have a cell C5 that have the following four numbers separated by a comma :
122.20,358.55,155.58,758.33
在这里,您可以使用拆分"功能返回一个数组,该数组存储单元格C5中的每个数字:
Here is how you can use the Split function to return an array storing each number from cell C5:
msgbox(split(range("c5"),",")(0)) 'This will return 122.20
msgbox(split(range("c5"),",")(1)) 'This will return 358.55
msgbox(split(range("c5"),",")(2)) 'This will return 155.58
msgbox(split(range("c5"),",")(3)) 'This will return 758.33
希望这会有所帮助
这篇关于VBA/EXCEL:从一个包含多个逗号值的单元格中提取数字的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!