標題: [發問] 請問如何將各欄位數值分割後，再乘上對應碼後加總? [打印本頁]

---

作者: winston219    時間: 2010-12-23 22:34     標題: 請問如何將各欄位數值分割後，再乘上對應碼後加總?

如附檔說明，謝謝![attach]4196[/attach]

---

作者: oobird    時間: 2010-12-23 23:14

1. Sub yy()
   
2.     Dim rng, arr, t$, w$, i%, j%
   
3.     With Sheet1
   
4.         rng = .Range(.[a2], .[g65536].End(3))
   
5.     t = "12345678901234567890123"
   
6.     ReDim arr(1 To UBound(rng), 0)
   
7.     For i = 1 To UBound(rng)
   
8.         w = rng(i, 2) & rng(i, 3) & rng(i, 4) & rng(i, 5) & rng(i, 6) & rng(i, 7)
   
9.         For j = 1 To 23
   
10.             x1 = Mid(w, j, 1) * Mid(t, j, 1)
    
11.             arr(i, 0) = arr(i, 0) + x1
    
12.         Next
    
13.     Next
    
14.     .[h2].Resize(i - 1, 1) = arr
    
15.     End With
    
16. End Sub

複製代碼

---

作者: winston219    時間: 2010-12-23 23:27

oobird大大，您真是太神了! 感謝您~

---

作者: Hsieh    時間: 2010-12-23 23:34

小弟也來試試

1. Sub nn()
   
2. For Each a In Range([B2], [B65536].End(xlUp))
   
3.     mystr = Join(Application.Transpose(Application.Transpose(a.Resize(, 6))), "")
   
4.     For i = 1 To 23
   
5.        cnt = cnt + (i Mod 10) * Mid(mystr, i, 1)
   
6.     Next
   
7.     a.Offset(, 6) = cnt: cnt = 0
   
8. Next
   
9. End Sub

複製代碼

---

作者: GBKEE    時間: 2010-12-24 11:43

回復 1# winston219

1. Sub Ex()
   
2.     Dim Ar(), Ar1(), A, MyStr, I
   
3.     For Each A In Range("B2", Range("B" & Rows.Count).End(xlUp).Address)
   
4.         MyStr = Join(Application.Transpose(Application.Transpose(A.Resize(, 6))), "")
   
5.         ReDim Ar(Len(MyStr) - 1)
   
6.         ReDim Ar1(Len(MyStr) - 1)
   
7.         For I = 1 To Len(MyStr)
   
8.             Ar(I - 1) = Val(Right(I, 1))
   
9.             Ar1(I - 1) = Val(Mid(MyStr, I, 1))
   
10.         Next
    
11.         A.Offset(, 6) = Application.SumProduct(Ar, Ar1)
    
12.     Next
    
13. End Sub

複製代碼

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)