电子物料清单拆分异常，求帮忙

雪之花 · 发表于 2022-4-26 15:18

最近忙于SMT编程，电子物料清单拆分遇到一点难题，求论坛大神老师帮忙解决，非常谢谢

雪之花 · 发表于 2022-4-26 19:32

有没有大神老师帮忙修改里面错误的，非常感谢

zpy2 · 发表于 2022-4-26 20:06

select * from SMT电子物料清单拆分 limit 3; cli_split_data~SMT电子物料清单拆分~[、，；\s]~备注; create temp table aa as select 代号,regreplace('[\~～]','-',备注) 备注 from SMT电子物料清单拆分split; create temp table bb as select 代号,备注,iif(instr(备注,'-')>0,regexp2('^[a-zA-Z]+',备注),'') 备注2,iif(instr(备注,'-')>0,regreplace('[a-zA-Z0]+','',备注),'') 备注3 from aa; create temp table cc as select *,split_num(备注3) 连续 from bb; cli_split_data~cc~,~连续; create temp table dd as select iif(备注2='',备注,printf(备注2||'%02d',连续)) 新备注,代号 from ccsplit; select regreplace('(?<=\D)\d(?=$)','0\0',新备注) 新备注,代号 from dd;

雪之花 · 发表于 2022-4-26 21:12

本帖最后由雪之花于 2022-4-26 21:36 编辑

zpy2 发表于 2022-4-26 20:06
select * from SMT电子物料清单拆分 limit 3; cli_split_data~SMT电子物料清单拆分~[、，；\s]~备注; creat ...

大师的回复，有点看不懂啊，图片结果是对的

zpy2 · 发表于 2022-4-27 08:02

雪之花发表于 2022-4-26 21:12
大师的回复，有点看不懂啊，图片结果是对的

经供参考。

雪之花 · 发表于 2022-4-27 23:47

论坛其他高手能否帮忙解答的，非常感谢

雪之花 · 发表于 2022-4-28 18:58

zpy2 发表于 2022-4-26 20:06
select * from SMT电子物料清单拆分 limit 3; cli_split_data~SMT电子物料清单拆分~[、，；\s]~备注; creat ...

老师，请问你的代码如何可以实现我要的功能，可以指点下么，非常感谢

zpy2 · 发表于 2022-4-29 15:58

雪之花发表于 2022-4-28 18:58
老师，请问你的代码如何可以实现我要的功能，可以指点下么，非常感谢

5楼请参考一下。

雪之花 · 发表于 2022-5-7 14:32

程序自己修改了下，结果备注栏单元格数字前带0的输出结果没有0，麻烦高手帮忙修改下，或者有好的代码提供下，万分感谢。

microyip · 发表于 2022-5-8 02:26

Sub InfoList()
Dim vData As Variant, nRow As Long, nCol As Long, nI As Long, nJ As Long
Dim sConnect As String, dicConnectChar As Object
Dim nCodeCol As Long, nMemoCol As Long
Dim sMemo As String, sCode As String
Dim vDivide As Variant, sDivide As String, vConnect As Variant, sNum(1) As String, nLen As Long, nNum As Long, sSerial As String
Dim vFill As Variant, nFill As Long
Set dicConnectChar = CreateObject("Scripting.Dictionary")
With Sheet2
nRow = .Cells(.Rows.Count, 1).End(xlUp).Row
vData = .[A1:B1].Resize(nRow).Value
For nRow = 3 To UBound(vData)
If vData(nRow, 2) = "-" Then dicConnectChar(vData(nRow, 1)) = 0 '记录需要替换的相连符号
Next
sConnect = "" & Join(dicConnectChar.Keys(), "|") '组建正则查找字符对象
nRow = .Cells(.Rows.Count, 5).End(xlUp).Row
vData = .[D1:E1].Resize(nRow).Value
nCodeCol = vData(3, 2) '代码列号
nMemoCol = vData(5, 2) '备注列号
End With
With Sheet1
nRow = Application.WorksheetFunction.Min(.Cells(.Rows.Count, nCodeCol).End(xlUp).Row, .Cells(.Rows.Count, nMemoCol).End(xlUp).Row) '查找代码列、备注列的最小行数
nCol = Application.WorksheetFunction.Max(nCodeCol, nMemoCol) '查找代码列号、备注列号之中的最大数
vData = .[A1].Resize(nRow, nCol).Value
ReDim vFill(1 To 2, 0)
With CreateObject("Vbscript.RegExp")
.Global = True
.IgnoreCase = True
For nRow = 2 To UBound(vData)
sCode = vData(nRow, nCodeCol)
sMemo = vData(nRow, nMemoCol)
.Pattern = "[" & sConnect & "]+"
If .Test(sMemo) Then sMemo = .Replace(sMemo, "-") '替换相连符号为-号
.Pattern = "[^\-\w\d]+"
If .Test(sMemo) Then sMemo = .Replace(sMemo, ",") '替换非-号、非字母、非数字的符号为,号
vDivide = Split(sMemo, ",") '分离各个分开的段
For nI = LBound(vDivide) To UBound(vDivide)
sDivide = vDivide(nI)
vConnect = Split(sDivide, "-")
.Pattern = "\d+$"
sNum(0) = .Execute(vConnect(0))(0) '取最小序号部分
nLen = Len(sNum(0))
sSerial = Left(vConnect(0), Len(vConnect(0)) - nLen) '取非序号部分
If UBound(vConnect) = 1 Then '说明有相连
sNum(1) = .Execute(vConnect(1))(0) '取最大序号部分
Else
sNum(1) = sNum(0)
End If
For nNum = Val(sNum(0)) To Val(sNum(1))
nFill = nFill + 1
ReDim Preserve vFill(1 To 2, 1 To nFill)
vFill(1, nFill) = sSerial & Right("00000" & nNum, nLen)
vFill(2, nFill) = sCode
Next
Next
Next
End With
.[E:F].ClearContents
.[E1:F1].Resize(nFill) = Application.WorksheetFunction.Transpose(vFill)
End With
End Sub

复制代码

		自动登录	找回密码
密码			免费注册

[求助] 电子物料清单拆分异常，求帮忙

评分

评分