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