本帖最后由 夏之雨 于 2010-5-11 06:39 編輯
VBA自動化復制公式二法
首先感謝“LangQueS”、“富戈”和“zjdh”三位老師不厭其煩的幫助，以及貢獻了如此具有劃時代意義的思想！
經(jīng)過驗證，只要遵循下面的幾條規(guī)則以及注意事項，運用這兩種方法就完全不會出現(xiàn)任何錯誤！而且可以提高我們的工作效率十倍、百倍！可以減小我們的文件體積到幾十K、幾K！
從此我們再不用惆悵于工作表內(nèi)自編公式繁多帶來的種種麻煩，再不用擔心Excel文件的龐大！讓我們盡情編寫公式，因為只需簡單的幾步VBA修改、粘貼，那些困難將會一掃而光！
當然，現(xiàn)有方法里還有一些未盡完美的地方，需要更多的高手給予指教、修改、完善。我也希望擅長VBA的朋友能編寫出一個自動化修改系統(tǒng)，每次彈出幾個輸入框，只需填入需要修改的部分，然后自動生成新的、適合自己的代碼，我們僅僅將代碼復制自己的工作表中即可！這應該不難實現(xiàn)，期待有識之士能夠早日奉獻這樣的作品以便不熟悉代碼的朋友方便使用！
再次強調(diào)一定要遵循下面幾條規(guī)則，否則將會出現(xiàn)錯誤！
幾條規(guī)則：
1、兩種方法都要求我們的工作表盡可能的符合這樣的排列，即數(shù)據(jù)列排在最左邊，公式列（或者生成結果列）排在所有數(shù)據(jù)列的后面，當然，如果個別數(shù)據(jù)列與公式列交替排列也不會出現(xiàn)錯誤，但會給你的更新數(shù)據(jù)帶來麻煩；
2、兩種方法都要求我們的工作表必須避免首列為公式列，如果那樣將絕對出現(xiàn)錯誤；
3、不管是任何方法，建議我們都要養(yǎng)成好的管理數(shù)據(jù)的習慣，即首行為標題，以下行為數(shù)據(jù)或公式，數(shù)據(jù)和公式按同列歸類的原則放置，簡單說，同一列的公式都是相同的，服從向下填充。
如果你遵循了上述三條規(guī)則，那么你可以根據(jù)實際需要選擇下面任一種方法管理你的公式，但是還應該認真遵循每種方法后面的注意事項，避免意想不到的錯誤。
最后，再次感謝三位老師！衷心謝謝你們?。?！
LangQueS_VBA復制公式法
- Sub LangQueS_VBA復制公式法()
- i = Range("a65536").End(xlUp).Row
- If i =標題行行號，例如1 Then
- Exit Sub
- Else
- Application.ScreenUpdating = False '關閉屏幕刷新
- y = Columns(1).Find("*", , xlValues, , ,2).Row 'A列最后單元行號
- x = [IV標題行行號，例如1].End(xlToLeft).Column '返回第1行最右邊非空單元的列號
- For 列 = 1 To x '設置變量，遍歷所有列
- If Cells(所有公式列的首行所在的行號，例如2, 列).HasFormula = True Then '確定所有公式列的首行是第幾行
- Cells(所有公式列的首行所在的行號，例如2, 列).Copy Range(Cells(所有公式列的首行所在的行號，例如2, 列), Cells(y, 列)) '將該公式列的首行公式復制到以下所有單元格
- Range(Cells(所有公式列的第二行所在的行號，例如3, 列), Cells(y, 列)) = Range(Cells(所有公式列的第二行所在的行號，例如3, 列), Cells(y, 列)).Value '復制粘貼該公式列第三行到最后一行單元格數(shù)值
- End If
- Next
- Application.ScreenUpdating = True '打開屏幕刷新
- End If
- End Sub
復制代碼
【通用代碼】
- Sub LangQueS_VBA復制公式法()
- TextBox1.Visible = True
- i = Range("a65536").End(xlUp).Row
- If i = 1 Then
- Exit Sub
- Else
- Application.ScreenUpdating = False '關閉屏幕刷新
- y = Columns(1).Find("*", , xlValues, , , 2).Row 'A列最后單元行號
- x = [IV1].End(xlToLeft).Column '返回第1行最右邊非空單元的列號
- For 列 = 1 To x '設置變量，遍歷所有列
- If Cells(2, 列).HasFormula = True Then '確定所有公式列的首行是第幾行
- Cells(2, 列).Copy Range(Cells(2, 列), Cells(y, 列)) '將該公式列的首行公式復制到以下所有單元格
- Range(Cells(3, 列), Cells(y, 列)) = Range(Cells(3, 列), Cells(y, 列)).Value '復制粘貼該公式列第三行到最后一行單元格數(shù)值
- End If
- TextBox1.Value = String(Int(22 * 列 / x), "■")
- DoEvents
- Next
- Application.ScreenUpdating = True '打開屏幕刷新
- End If
- TextBox1.Visible = False
- MsgBox "運算完畢！ ", 64, " 提示 "
- End Sub
復制代碼
注意事項：
1、該方法智能化較高，它要求先確定標題行是第幾行以及所有公式列的首行是第幾行，每次追加數(shù)據(jù)后，運行宏，可得到最新的公式計算結果；
2、該方法操作比較簡單，只用簡單地修改一下代碼中的行號即可，免去了輸入公式的麻煩；
3、還應注意，首行的公式不要誤刪，否則將不能識別，產(chǎn)生無法挽回的后果；
富戈_VBA復制公式法
- Sub 富戈_VBA復制公式法()
- Application.ScreenUpdating = False '關閉屏幕刷新
- Dim y As Long
- y = Range("a65536").End(xlUp).Row+1
- Range("第一個公式的位置，例如E2: 第一個公式所在列，例如E" & y) = "復制原單元格第一個公式，例如=ROW()*2" '粘貼公式
- '重復上一步工作，將其它公式一一復制，注意：都是公式首行第一個公式！
- Range("第一個公式的位置，例如E2:
- 最后一個公式所在列，例如J" & y) = Range("第一個公式的位置，例如E2:
- 最后一個公式所在列，例如J " & y).Value '用運算后的數(shù)值替代公式
- Rows(y).Delete
- Application.ScreenUpdating = True '打開屏幕刷新
- End Sub
復制代碼
注意事項：
1、該方法的優(yōu)點在于使工作表內(nèi)看不到任何公式，每次追加數(shù)據(jù)后，運行宏，可得到最新的公式計算結果；
2、其缺點是，如果公式較多，即使是復制也會花費很長時間，而且要修改的代碼比較多。
| 
