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

[µo°Ý] excel VBA ¨ç¼Æ-««ª½·j´M¬Û¹ïÀ³¦Cªº­È

[µo°Ý] excel VBA ¨ç¼Æ-««ª½·j´M¬Û¹ïÀ³¦Cªº­È

¦U¦ì«e½ú¤j®a¦n
¤p§Ì³Ìªñ¦b¨Ï¥ÎEXCEL VBA°µ¸ê®Æ¾ã²z

¤p§Ì§Æ±æ¦bÀx¦s®æ¦C¤¤««ª½·j´M¥X¬Û¹ïÀ³¦Cªº­È(¨Ò¦p:¦b«ü©w·j´M½d³ò¤º, ·j´M²Äm¦Cªº­È, ·í²Äm¦C¬Y¦æªº­Èµ¥©ó"±ý·j´Mªº­È"®É¡A«h¦^¶Ç²Än¦C¦P¦æªº­È)
¥Ø«e¦bºô¸ô¤W¥u§ä¨ìVLOOKUP©MLOOKUP³o¨â­Ó¨ç¼Æ¥i¥H¦³Ãþ¦üªº¥\¯à
¦ýVLOOKUP ¨ç¼Æ ­­¨î"±ý·j´Mªº­È"¥u¯à¦b«ü©w·j´M½d³òªº²Ä¤@¦C
¦ÓLOOKUP  ¨ç¼Æ "±ý·j´Mªº­È"¤S­­¨î¥u¯à¥H»¼¼Wªº¶¶§Ç±Æ¦C

·Q½Ð°Ý¦U¦ì«e½ú°£¤F³o¨â­Ó¨ç¼Æ¤§¥~
¦³¨S¦³¨ä¥L¦n¥ÎªºVBA¨ç¼Æ¥i¥H¦³Ãþ¦üªº¥\¯à
¦ý¤£·|¦³¥H¤W­­¨îªº

³Â·Ð¦U¦ì«e½ú«ü¾É¤F~~ÁÂÁÂ~~!

¦^´_ 1# junkwei
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Rng As Range
  4.     Set Rng = Sheets("«ü©w¤u§@ªí").Range("«ü©w½d³ò").Find("·j´M¦r¦ê", LookAT:=xlWhole)
  5.     If Not Rng Is Nothing Then MsgBox Rng.Value
  6. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 1# junkwei
ªþÀÉÁ|­Ó§A¹ê»Ú¹J¨ìªº¨Ò¤l&§Æ±æªºµ²ªG¡A°Ý¨ìªºµª®×·|ºë·Çªº¦h¡C¡C
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 2# GBKEE


GBKEE ª©¥D±z¦n :

½Ð°Ý¤@¤U¡A­Y§Ú¦³¦h­Ó­n·j´Mªº¸ê®Æ¸Ó¦p¦ó¤@­Ó¤@­Óªº¨ú¥N¦¨¨ä¥Lªº­È¡A   

¨Ò¦paÄæ¨ìcÄ椤ªº¡ytest¡z¨ú¥N¦¨¡ytt¡z!!

TOP

¦^´_ 2# GBKEE

GBKEE ª©¥D±z¦n:
¤£¦n·N«ä¤p§Ìªì¾ÇVBA
±zªºcode ¤p§ÌÁÙ¬O¦³¨ÇÀ³¥Î¤Wªº§xÃø..
¦]¦¹¨Ìstillfish00«e½úªº«Øij
±N§Úªº°ÝÃD§¹¾ã§e²{
¦A³Â·Ðª©¥D¼·ªÅ«ü¾É¤F...
«D±`·PÁÂ...!!

««ª½·j´M.png (21.51 KB)

««ª½·j´M.png

TOP

¦^´_ 3# stillfish00

stillfish00«e½ú±z¦n:
¤£¦n·N«ä¤p§Ìªº°ÝÃD±Ô­z¦ü¥G¯uªº¤£¤Ó²M·¡
¦]¬°§Ú­ì¥»ªº¥Øªº¬O¥´ºâ¥u°Ý¨º­Ó¨ç¼Æªº¦WºÙ
¦A¦Û¤v¥h§ä¨ç¼Æªº¬ÛÃö¸ê°T¥hÀ³¥Î
¤£®Æ¸ê½è¾q¶w....0rz
¦n¹³°ÝÃD±Ô­zªº¤£§¹¥þ,
«e½ú­Ì©Ò¼gªºµ{¦¡¤]¤£¨£±o¥i¥H§¹¥þÀ³¥Î
¤U¦¸¤p§Ìµo¤å®É·|¯S§Oª`·Nªº~!
¤]·PÁ«e½úªº¤£§[«ü±Ð~~!

TOP

¦^´_ 5# junkwei
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Rng, Ar, R As Range, i As Integer, ii As Integer
  4.     Ar = Range("A1").CurrentRegion.Value    '¸ê®Æ®w
  5.     i = [MATCH(J1,A1:D1,0)]
  6.     '©Îª½±µµ¹ I=3  '¼Æ¾Ç¦¨ÁZ¦b¸ê®Æ®wªºÄæ¦ì
  7.     Set Rng = Range("H1", Range("H1").End(xlDown)).Resize(, 2)
  8.     For Each R In Rng.Rows
  9.         For ii = 1 To UBound(Ar, 1)
  10.             If R.Cells(1, 1) & R.Cells(1, 2) = Ar(ii, 1) & Ar(ii, 2) Then
  11.                 R.Cells(1, 3) = Ar(ii, 3)
  12.                 Exit For
  13.             End If
  14.         Next
  15.     Next
  16. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 5# junkwei
¨Ì5¼Ó¹Ïªº°ÝÃD¡A³Ì²³æªº¤èªk¬O
¦bJ2¶ñ¤W =LOOKUP(2,1/((B$1:B$16=H2)*(C$1:C$16=I2)),A$1:A$16)
µM«á©¹¤U¶ñº¡¤½¦¡¡A
³o¤½¦¡ºâ¬O¤ñ¸û¶i¶¥¤SÁÙÆZ±`¹J¨ìªº¤@­Ó¤p§Þ¥©¡C

­Y­n¥ÎVBA´N°Ñ¦ÒGª©ªº§a~
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_  junkwei
¸Õ¸Õ¬Ý
GBKEE µoªí©ó 2014-12-15 08:12


ÁÂÁÂGBKEEª©¥DªºÀ°§U!!¤SÅý¤p§Ì¦A¦¸¨£ÃѤF¤@¦¸vbaªº«Â¤O!
¤£¹L±z­ì¥»ªºcode¦n¹³Äæ¦ì¦³¨Çµ§»~
¦]¦¹¤p§Ì¦³¤p¤p­×¥¿¤F¤@¤U, ¦p¤U©Ò¥Ü
¨Ñ¨ä¥L¦³»Ý­nªºª©¤Í°Ñ¦Ò~~
³Ì«á¦A¦¸·PÁ±zªºÀ°¦£~!
  1. Sub CAL2()
  2.     Dim Rng, Ar, R As Range, i As Integer, ii As Integer
  3.     Ar = Range("A1").CurrentRegion.Value    '¸ê®Æ®w
  4.     i = [MATCH(J1,A1:D1,0)]
  5.     '©Îª½±µµ¹ i=1  '¼Æ¾Ç¦¨ÁZ¦b¸ê®Æ®wªºÄæ¦ì
  6.     Set Rng = Range("H1", Range("H1").End(xlDown)).Resize(, 2)   
  7.    
  8.     For Each R In Rng.Rows  
  9.         For ii = 1 To UBound(Ar, 1)
  10.             If R.Cells(1, 1) & R.Cells(1, 2) = Ar(ii, 2) & Ar(ii, 3) Then
  11.                 R.Cells(1, 3) = Ar(ii, 1)
  12.                 Exit For   
  13.             End If
  14.         Next
  15.     Next
  16.    
  17. End Sub
½Æ»s¥N½X

TOP

¦^´_  junkwei
¨Ì5¼Ó¹Ïªº°ÝÃD¡A³Ì²³æªº¤èªk¬O
¦bJ2¶ñ¤W =LOOKUP(2,1/((B$1:B$16=H2)*(C$1:C$16=I2)),A$ ...
stillfish00 µoªí©ó 2014-12-15 16:40


·PÁÂstillfish00«e½úªº¤èªk~
Åý§Ú¾Ç¨ì¤£¦P¥B¹ê¥Îªº¤èªk~ÁÂÁ±z~~!
¤£ª¾¹D«e½ú¬O§_¤è«K»¡©ú¤@¤U±zªº¤½¦¡¤¤lookup¨ç¼Æªº²Ä¤GÄæ
¥H«K¤p§Ì¤é«áÀ³¥Î

½Ð°Ý
1/(½d³ò=±ý·j´M­È)  ¬O¤@ºØ©T©wªº»yªk¶Ü?
¥H¤Î
·í¤¤ªº * ²Å¸¹  ¦b³o¸Ì¥Nªíªº·N¸q¬O"¥B"ªº·N«ä¶Ü?

¤p§Ì¦bºô¸ô¤W¦n¹³¤£®e©ö§ä¨ì¬ÛÃöªº»¡©ú...@@
¤£¦n·N«ä¦A³Â·Ð«e½ú«ü¾É¤F!
ÁÂÁÂ~~!

TOP

        ÀR«ä¦Û¦b : ­n§åµû§O¤H®É¡A¥ý·Q·Q¦Û¤v¬O§_§¹¬üµL¯Ê¡C
ªð¦^¦Cªí ¤W¤@¥DÃD