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

[µo°Ý] ¦p¦óŪ¨ú¯x°}¸ê®Æªº°ÝÃD

Àx¦s®æ¹ïÀ³ªº°ÝÃD

§Ú·Q±qtable1 ¥ô¦ócell, ¥htable2§ä´M¹ïÀ³ªºcell

Ex: §Ú¿ï©wtable 1¡§C6¡¨ , table2 ¹ïÀ³ªº¬OC14
¸Ó¦p¦ó¼g©O?

Àx¦s®æ¹ïÀ³.rar (5.94 KB)

TOP

¦^´_ 1# Helain
  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2. If Intersect(Target, [C6:F9]) Is Nothing Then Exit Sub
  3. MsgBox "¿ï¨ú" & Target.Address
  4. MsgBox "¹ïÀ³" & Application.Index([C14:F17], Target.Row - 5, Target.Column - 2).Address
  5. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-11-2 07:29 ½s¿è

¦^´_ 1# Helain
  1. Private Sub Workbook_Open()  'ThisWorkbook ªºµ{§Ç Àɮ׶}±Ò®É¦Û°Ê°õ¦æ
  2.     Ex
  3. End Sub
  4. Private Sub Ex()             'ThisWorkbook ªºµ{§Ç
  5.     With Sheet1
  6.         .Names.Add "table1", .Range(.[C6], .[C6].End(xlToRight).End(xlDown))   '©w¸q¦WºÙ
  7.         .Names.Add "table2", .Range(.[C14], .[C14].End(xlToRight).End(xlDown)) '©w¸q¦WºÙ
  8.    End With
  9. End Sub
½Æ»s¥N½X
  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range) '¤u§@ªí1ªºµ{§Ç
  2.     Dim R As Double, C As Double
  3.     If Intersect(Target, [table1]) Is Nothing Then Exit Sub
  4.     R = Target.Row - [table1].Cells(1).Row + 1
  5.     C = Target.Column - [table1].Cells(1).Column + 1
  6.     MsgBox "¿ï¨ú" & Target(1).Address(0, 0) & "  ¹ïÀ³ " & [table2].Cells(R, C).Address(0, 0)
  7. End Sub
½Æ»s¥N½X

TOP

[µo°Ý] ¦p¦óŪ¨ú¯x°}¸ê®Æªº°ÝÃD

§Ú·Q­n°Ý,¦p¦óŪ¨ú¯x°}¸ê®Æªº°ÝÃD
¦³2­ÓÀÉ®×, table A, table B

tableB¬O¯x°}¸ê®Æ

table A ¤]¬O§e²{¯x°}ªº¤è¦¡¥h¹Bºâ

¦p:
i=1 j = 5,
A=4 ¦C
B=5 Äæ

"¦p¦ó¥Î¤£¦PªºA,B¥hŪ¨útable Bªº

cells(3,7).value= 27 "Ū¨útableB ªº¼Æ­È

Read cell.rar (14.54 KB)

¨ä¹ê§Ú¬Ý¤£¤ÓÀ´¨â¦ì¤j¤j´£¥Xªº·Qªk,
¦pªG¥ý·Q²³æ¤@ÂI¡A §Ú¦³2­Ó¦æ¦C¼Æ¬Û¦Pªºtable¦b¦P¤@­Ósheet¸Ì.

§Ú¿ï¨úTable A¨ä¤¤¤@­Ócell¦ì¸m®É, ¤]·Q¦btable B §ä¥X»Ptable A¬Û¹ïÀ³ªº­È
¦pªG¥ÎOFFSET³o­Ó¨ç¼Æ¨Ó°µ©O?
¤£ª¾¹D­n¦p¦óŪ¨úTable A ©Ò¿ï¨úªº¦ì¸m¸ê°T
table Bªº¹ïÀ³­È=OFFSET(tableB°Ñ¦Ò­È , ¦btableA ¿ï¨úªº¦C, table B¿ï¨úªºÄæ )

ÁÙ¬O¦³¨ä¥L¤èªk¥i¥H©O??

·PÁ¤j¤j­Ì¬°§Ú¸Ñµª

TOP

¦^´_ 5# Helain

§@¥Î¤¤Àx¦s®æ´N¬OActiveCell
ActiveCell.Row¨ú±o§@¥ÎÀx¦s®æ¦C¸¹
ActiveCell.Column¨ú±o§@¥ÎÀx¦s®æÄ渹
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

§Ú·Q­n°Ý»¡
°²³]¦³¤@°ï¼Æ­È¸ê®Æ, ¿ï¨ú³Ì¤j­È
·Q±o¨ì¸Ó³Ì¤j­Èªº¦ì¸m
´N¬O¥t¥L¿ï¨ìªº¨º­Ó­È¬°activecell
¸Ó¦p¦ó°µ©O?

TOP

¥i¥H¬°§Ú¸ÑÄÀ¤@¤UIntersectªº¥Îªk¶Ü?

If Intersect(Target, [table1]) Is Nothing Then Exit Sub
³q±`¼gis nothing.....¬O«ü¤°»ò·N«ä

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2011-11-6 21:34 ½s¿è

¦^´_ 8# Helain
§Úªº²z¸Ñ¬O :
Is Nothing ¤f»y¤Æªº¸ÑÄÀ¤j¬ù´N¬O "¤°»ò³£¨S¦³",
¤Þ¦ù¹L¨Ó VBA ´N¬O§ä¤£¨ì²Å¦X³B²zµ²ªG(©Î­pºâ±ø¥ó)ªº¼Ðªº,
¨ä·N¤j¬ù¤]´N¬O "ªÅ­È"(NULL).

¦ý§Ú¸Õ¹L­Y§ï¦¨
If Intersect(Target, [table1]) ="" Then Exit Sub ·|¦³¿ù»~,
¦ý­ì¦]§Ú´N·Q¤£³z¤F.

¦^´_ 10# Hsieh
ÁÂÁ§Aªº¸Ñ»¡,
³o¼Ë§Ú´N¤F¸Ñ¤F.

TOP

¦^´_ 9# luhpro

Intersect·|¶Ç¦^Rangeª«¥ó
·íª«¥óÅܼƬ°«D¹êÅ骫¥ó®É§Y¬°Nothing
©Ò¥H¡AIntersect¥²¶·¥HNothingÃöÁä¦rªí¥Ü«D¹êÅ骫¥ó
­Y¨Ï¥ÎªÅ¦r¦ê""¡A¦¹¬°¦r¦ê«¬ºA¤§µL¦r¦êªí¥Ü
©Ò¥HIntersect¨ç¼Æ­pºâµ²ªG¡A­Y¨Ï¥ÎªÅ¦r¦ê±N¶Ç¦^¸ê®Æ«¬ºA¿ù»~
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : µÊ®ð¼L¤Ú¤£¦n¡A¤ß¦a¦A¦n¤]¤£¯àºâ¬O¦n¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD