Hsieh

¦^´_ 1# fangsc
VLOOKUP¶Ç¦^¨Ã«DÀx¦s®æª«¥ó¡A¦Ó¬OÀx¦s®æªº­È
¤º«Ø¨ç¼ÆµLªk¨ú±oµù¸Ñ¤º®e¡A¼g­Ó¦Û©w¸q¨ç¼Æ¸Ñ¨M

µù¸Ñ¤å¦r.zip (6.2 KB)

¤U¸ü¦¸¼Æ: 28

2012-8-22 14:34

Hsieh

¦^´_ 3# fangsc

¤£À´§Aªº·N«ä¡A­n¥ÎVBA¥[¤JÀx¦s®æµù¸Ñ·íµM¥i¦æ

1. Sub nn()
   
2. If Range("C3").Comment Is Nothing Then 'C3Àx¦s®æ¨S¦³µù¸Ñ
   
3.   Range("C3").AddComment "TEST"  ''C3Àx¦s®æ¥[¤Jµù¸Ñ
   
4.   Else  '§_«h
   
5.    Range("C3").Comment.Text Range("C3").Comment.Text & Chr(10) & "addtext"  '¥[¤Jµù¸Ñ¤å¦r
   
6. End If
   
7. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 5# fangsc

¤U¸ü (22.63 KB)

2012-8-22 22:00

Hsieh

¦^´_ 7# fangsc

¤U¸ü (14.29 KB)

2012-8-23 09:55

1. Sub ex()
   
2. Dim Rng As Range, A As Range
   
3. For Each A In Range("J4").CurrentRegion.SpecialCells(xlCellTypeFormulas)
   
4. If A.FormulaLocal Like "=VLOOKUP(*,*,*,*)" Then
   
5.   ar = Split(Replace(Replace(A.FormulaLocal, "VLOOKUP(", ""), ")", ""), ",")
   
6.   Set Rng = Range(ar(1))
   
7.   k = Application.Match(Range(ar(0)), Rng.Columns(1), 0)
   
8.   If Not A.Comment Is Nothing Then A.Comment.Delete
   
9.   If Not Rng(k, Val(ar(2))).Comment Is Nothing Then
   
10.     A.AddComment Rng(k, Val(ar(2))).Comment.Text
    
11.   End If
    
12. End If
    
13. Next
    
14. End Sub

½Æ»s¥N½X

µù¸Ñ¤å¦r.zip (12.29 KB)

¤U¸ü¦¸¼Æ: 16

2012-8-23 09:53

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