Hsieh

¦^´_ 10# jsc0518
·f°t­«ºâ¨Æ¥ó

1. Private Sub Worksheet_Calculate() '¶È¾A¥ÎVLOOKUP¨ç¼Æ
   
2. Dim A As Range, C As Range, Rng As Range, Sht As Worksheet, Mc As Comment
   
3. For Each A In Sheet1.UsedRange.SpecialCells(xlCellTypeFormulas)
   
4. If A.FormulaLocal Like "=VLOOKUP(*,*,*,*)" Then '¬O§_¬OVLOOKUP¨ç¼Æ
   
5. fx = Split(Split(A.Formula, "(")(1), ",") '¤½¦¡¤À¸Ñ
   
6. Set Rng = Sheets(Split(fx(1), "!")(0)).Range(Split(fx(1), "!")(1)) '¨Ó·½¸ê®Æ½d³ò
   
7.     x = Range(fx(0)) '²Ä¤@Ä檺·j´M­È
   
8.     r = Rng.Find(x).Row '§ä¨ì¨Ó·½¸ê®Æªº¦C¸¹
   
9.     k = Val(fx(2)) + Rng.Column - 1 '¨Ó·½¸ê®Æ¦ì©ó¾ã­Ó¤u§@ªíªºÄ渹
   
10.     If Not A.Comment Is Nothing Then A.Comment.Delete '§R°£¤½¦¡Àx¦s®æ¤ºªºµù¸Ñ
    
11.     Set Mc = Rng.Parent.Cells(r, k).Comment '¨Ó·½¸ê®Æªºµù¸Ñ
    
12.     If Not Mc Is Nothing Then A.AddComment Rng.Parent.Cells(r, k).Comment.Text '¥[¤J¨Ó·½µù¸Ñ
    
13. End If
    
14. Next
    
15. End Sub

½Æ»s¥N½X

jsc0518

¦^´_ 11# Hsieh
Dear Hsieh,
·PÁ±zªº¼ö¤ß¦^ÂСA¦³­Ó°ÝÃD»P±z½Ð±Ð

.­Y"¨Ó·½¸ê®Æ"¬O¦bSheet3ªºF4Äæ¦C¶}©l¡A¤U­±»yªk»Ý­n­×§ï­þ¸Ì©O¡H
Set Rng = Sheets(Split(fx(1), "!")(0)).Range(Split(fx(1), "!")(1)) '¨Ó·½¸ê®Æ½d³ò


ÁÂÁ±z