標題: [分享] 分享 [打印本頁]

---

作者: mhl9mhl9    時間: 2013-7-15 21:10     標題: 分享

以下Test,執行一次,activesheet英文全部改為"大楷,再執行一次英文全部改為"小楷"
Sub LUcase(A As Boolean)
Dim R As Range
Dim R1 As Range
Dim R2 As Range
Dim cell As Range
Dim cell1 As Range

For Each cell In ActiveSheet.UsedRange.Areas
    Set R1 = cell.Resize(, 1)
    Set R2 = cell.Resize(1)
    If A = True Then
        cell = Evaluate("IF(ROW(" & R1.Address & "),iF(COLUMN(" & _
            R2.Address & "),upper(" & cell.Address & ")))")
    Else
        cell = Evaluate("IF(ROW(" & R1.Address & "),iF(COLUMN(" & _
            R2.Address & "),lower(" & cell.Address & ")))")
    End If
Next cell
End Sub

Sub test()
Static A As Boolean
LUcase A
A = Not A
End Sub

---

作者: GBKEE    時間: 2013-7-16 08:39

回復 1# mhl9mhl9
簡化一下

1. Option Explicit
   
2. Sub Ex()
   
3.      Dim E As Range
   
4.      Static A As Boolean
   
5.      For Each E In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
   
6.         E = IIf(A, UCase(E), LCase(E))
   
7.      Next
   
8.      A = Not A
   
9. End Sub

複製代碼

---

作者: mhl9mhl9    時間: 2017-11-13 13:35

GBKEE,謝謝提供簡易方法,很好用,但usedrange很大時很慢(我的個案110000行資料),還是謝謝

---

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