展开全部
应该不难,我做一个试试,别忙采纳
追答:
在vba编辑器中插入模块,代码如下:
Sub b()
Set sh1 = Sheet1
Set sh2 = Sheet2
sh1.Range("a3:a65536").ClearContents
sh2.Range("a2:a65536").ClearContents
Dim i As Long
Dim j As Long
Dim ii As Long
Dim zjz As Variant '定义中间值,起始值,终止值,冠号
Dim qsz As Variant
Dim zzz As Variant
Dim gh As Variant
ii = 2
sh1.Select
For i = 3 To 65536
For j = 2 To Range("iv" & i).End(xlToLeft).Column
If Cells(i, 2) = "" Then
Exit For
End If
zjz = Split(Cells(i, j), "-")
gh = Split(zjz(0), "(")(0)
qsz = Split(zjz(0), "(")(1)
zzz = Split(zjz(1), ")")(0)
Cells(i, 1) = zzz - qsz + 1 + Cells(i, 1)
Do While qsz <= CLng(zzz)
sh2.Cells(ii, 1) = "(" & gh & "-" & qsz & ")"
qsz = qsz + 1
ii = ii + 1
Loop
Next j
Next i
End Sub
效果如下(两个表,表名称默认,如果想改名字,需要改代码中的名字):
sheet1表样式(黄色区域为输入部分,总数量是自动计算的,测试过,吻合):
sheet2表样式:
此表只要不超过excel表限制,可以做至少几千列数据
展开全部
条件到结果的位置如何确定?结果是两列么?
Option Explicit
Sub 按照A列数据进行填充()
Dim S$, nB&, nC&, nS&, nE&, i&, j&, k&, Arr, l&, n&
k = 0
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
n = 0
S = Cells(i, "A")
nB = Val(Split(S, " ")(0))
S = Split(S, " ")(1)
Arr = Split(S, ")")
For j = 0 To UBound(Arr) - 1
nC = Val(Split(Arr(j), "(")(0))
nS = Split(Split(Arr(j), "(")(1), "-")(0)
nE = Split(Arr(j), "-")(1)
For l = nS To nE
k = k + 1: n = n + 1
Cells(k, "B") = n: Cells(k, "C") = "'" & nC & "-" & l
Next l
Next j
If n <> nB Then MsgBox "Err at " & i
Next i
End Sub
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
要用代码解决,你这条件没写好,不规范
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询