本帖最后由 龙送农 于 2013-12-2 15:27 编辑
如何用代码实现函数的这个效果:从I5:I298将数字分解到AB5:AK298
函数:AB5=IF(LEN(I5*100)>=10,MIDB(I5*100,LEN(I5*100)-9,1),"")
AC5=IF(LEN(I5*100)>=9,MIDB(I5*100,LEN(I5*100)-8,1),"")
AD5=IF(LEN(I5*100)>=8,MIDB(I5*100,LEN(I5*100)-7,1),"")
AE5=IF(LEN(I5*100)>=7,MIDB(I5*100,LEN(I5*100)-6,1),"")
AF5=IF(LEN(I5*100)>=6,MIDB(I5*100,LEN(I5*100)-5,1),"")
AG5=IF(LEN(I5*100)>=5,MIDB(I5*100,LEN(I5*100)-4,1),"")
AH5=IF(LEN(I5*100)>=4,MIDB(I5*100,LEN(I5*100)-3,1),"")
AI5=IF(LEN(I5*100)>=3,MIDB(I5*100,LEN(I5*100)-2,1),"")
AJ5=IF(LEN(I5*100)>=2,MIDB(I5*100,LEN(I5*100)-1,1),"")
AK5=IF(I5=0,"",MIDB(I5*100,LEN(I5*100),1))
本帖最后由 xdragon 于 2013-12-2 13:10 编辑
龙送农 发表于 2013-12-2 12:36
小数后两位数没实现,如果小数后两位数有数字就填数字,无数字就填0;另加K5:K298数字分解到AM5:AV298和N ... - Sub SplitArea()
- SPLITDATA Range("I5:I298"), Range("AB5")
- SPLITDATA Range("K5:K298"), Range("AM5")
- SPLITDATA Range("N5:N298"), Range("AY5")
- End Sub
- Sub SPLITDATA(DataSource As Range, OutArea As Range)
- If DataSource.Columns.Count > 1 Then MsgBox "只允许一列转换", , "提示": Exit Sub
- Dim arr, brr(), i As Integer, j As Integer, str As String
- arr = DataSource.Value
- ReDim brr(1 To UBound(arr), 1 To 10)
- For i = 1 To UBound(arr)
- str = IIf(arr(i, 1) = 0, "", Replace(CStr(Format(arr(i, 1), "0.00")), ".", ""))
- For j = 10 - Len(str) + 1 To 10
- brr(i, j) = Mid(str, j - 10 + Len(str), 1)
- Next
- Next
- OutArea.Resize(UBound(brr), 10) = brr
- End Sub
复制代码
|