ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 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
Just do it.

TOP

        ÀR«ä¦Û¦b : ¥¬¬I¦p¼½ºØ¡A¥HÅw³ß¤ß´þ¼íºØ¤l¡A¤~·|µoªÞ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD