Àx¦s®æ¸Ìªºµù¸Ñ

fangsc

½Ð±Ð:
¥ÎVlookup§ä¥XªºÀx¦s®æ¤º®e, ¦p¦ó¤~¯à±Nµù¸Ñ¤]¤@°_±a¹L¨Ó ?
¬O§_¦³¨ç¼Æ¥i¥H¥Î?

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

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

¦^´_ 8# Hsieh

Dear Hsieh,
¦­¦w¡I½Ð±Ð¤@¤U¡A­Y¨Ó·½¸ê®Æ¬O¥t¤@­Ó¤u§@ªí¡A¨º»yªk­n§ïÅÜ­þ¸Ì©O¡H
µù¸Ñ¤å¦rV2.zip (13.89 KB)

¤U¸ü¦¸¼Æ: 6

2023-6-29 08:21

ÁÂÁ±z¡I¡I

fangsc

·PÁª©¥D,¥Ø«e¥i¥H¥Î¤F.
¤£¹L,¬Ý°_¨Ó¦³ÂIÃø,§Ú±o¦A¿Ä·|³e³q¤@¤U,
¤~¯à¹B¥Î±o¦n¤@¨Ç.

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¸ü¦¸¼Æ: 15

2012-8-23 09:53

fangsc

¦^´_ 6# Hsieh

¹ï¤£°_,§ÚÁÙ¬O¨Sªí¹F²M·¡.
    ¦b¥Îvlookup¤§«á, ·Q¦Arunµ{¦¡±N¨Ó·½¸ê®Æ¸Ìªºµù¸Ñ¥[¶i¥Ø¦a¸ê®Æ  "Àx¦s®æªºµù¸Ñ" ¸Ì.
     (´N¬O§â¨Ó·½¸ê®Æªºµù¸Ñ,±a¶i¥Ø¦a¸ê®ÆÀx¦s®æªºµù¸Ñ¸Ì)
©êºp & ·PÁÂ...

Hsieh

¦^´_ 5# fangsc

¤U¸ü (22.63 KB)

2012-8-22 22:00

fangsc

¦^´_ 4# Hsieh

¹ê¦b©êºp,§Ú¨S¦³»¡²M·¡,®ö¶O¤F§Aªº®É¶¡.
¦b¥Îvlookup¤§«á, ·Q¦Arunµ{¦¡±N¨Ó·½¸ê®Æ¸Ìªºµù¸Ñ¥[¶i¥Ø¦a¸ê®ÆªºÀx¦s®æ¸Ì.
·PÁ§A.

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