[µo°Ý] Excel VBA ½Æ»s°ÝÃD½Ð¯q

zz0660

EXCEL¸Ì­±¦³¨â­Ó¤u§@ªí¡A¤@­Ó¦WºÙ¬°AA¡A¥t¤@­Ó¦WºÙ¬°ZZ¡C¦p¤U¹Ï
AA¤u§@ªí¡õ

¤U¸ü (38.82 KB)

2021-10-30 09:50

ZZ¤u§@ªí¡õ

¤U¸ü (38.43 KB)

2021-10-30 09:53

AA¤u§@ªí¡AA2ªº¦ì¸m¦³¤U©Ô¦¡¿ï³æ¡A¸Ì­±¦³20¡A40¡A60¡A80¡A100¡A121¡A142ªº¼Æ¦r¡AµM«á¥k°¼¦³¤@­Ó«ö¶s¡A·í«ö¤U¥k°¼«ö¶s´N·|§PÂ_A2ªº¼Æ¦r¡A°²³]1.°²³]AA¤u§@ªíªºA2=40¡A´N·|½Æ»sZZ¤u§@ªíªºE2¨ìE5ªº¼Æ¦r¡A°²³]2.AA¤u§@ªíªºA2=121¡A´N·|½Æ»sZZ¤u§@ªíªºE2¨ìE13ªº¼Æ¦r¡A°²³]3.AA¤u§@ªíªºA2=142¡A´N·|½Æ»sZZ¤u§@ªíªºE2¨ìE15ªº¼Æ¦r¡A³o¼Ë¦p¦ó¼gVBA§e²{¡C

¥H¤W¡A·Ð½Ð¤j¤j¸Ñ´b¡AÁÂÁÂ!

­ã´£³¡ªL

sub ½Æ»s()
dim v, m
v=val([AA!a2])
m=application.match(v, [{20,40,60,80,100,121,142}],0)
if iserror(m) then exit sub
[ZZ!e2].resize(m*2).copy [AA!B1]
end sub

zz0660

¦^´_ 2# ­ã´£³¡ªL

·PÁ¤j¤j¨ó§U³B²z°ÝÃD¡A¥¿¦n¬O§Ú·Q­nªº¼Ë¤l¡AÁÂÁ ­ã´£³¡ªL ¤j¤j!

zz0660

¦^´_ 2# ­ã´£³¡ªL



sub ½Æ»s()
dim v, m
v=val([AA!a2])
m=application.match(v, [{20,40,60,61,62,63,64,65,66,67,80,100,121,142}],0)
if iserror(m) then exit sub
[ZZ!e2].resize(m*2).copy [AA!B1]
end sub
    ¤j¤j¡A½Ð°Ý¤@¤U¡A°²³]§Ú¦A°l¥[¼Æ¦r61~67ªº¼Æ¦r¡A³o¼Ëªº¸ÜµLªk¥u³æ¯Â½Æ»s6®æ¡A¥Ø«e§Æ±æªº¬O°²³]60~69ªº¼Æ¦r¡A³£¥u·|½Æ»s6®æ¡A¤]´N¬OZZ¤u§@ªíªºE2:E7ªº®æ¤l¼Æ¡C
½Ð¤j¤j¸Ñ´b!   ¦³¸ÕµÛ§ï°Ê m*2 ªº*2 ¦ý®ÄªG¤£¦p¹w·Q¡C

­ã´£³¡ªL

¦^´_ 4# zz0660


¨º¦³¨S¦³68,69 ©Î¤£¦b²M³æ¤¤ªº¨ä¥¦¼Æ¦r,'
³Ì¦n¤W¶ÇªþÀÉ, ¨ÃÁ|¨Ò»¡©ú~~¦Ñ¬O¥Î²qªº¤Ó¶O®É¶¡

zz0660

¦^´_ 5# ­ã´£³¡ªL

´£¨ÑÀɮסA¦p¤U
AAZZ.rar (18.56 KB)

¤U¸ü¦¸¼Æ: 5

2021-10-30 21:30

  
³Â·Ð¤j¤j¤F¡AÁÂÁÂ!

­ã´£³¡ªL

¦^´_ 6# zz0660


Sub ½Æ»s()
Dim v, m
v = Val([AA!a2])
m = Application.Lookup(v, [{40,43,80,83,160,163,320,323}], [{2,0,4,0,6,0,8,0}])
If IsError(m) Then Exit Sub
If m > 0 Then [ZZ!e2].Resize(m).Copy [AA!B1]
End Sub

§ï¥Îlookup, ½Æ»s¦æ¼Æ¥i¦Û¥Ñ«ü©w¤£¦P°Ñ¼Æ, ¤£¤@©w¬O2©Î4ªº­¿¼Æ

.========================

zz0660

¦^´_ 7# ­ã´£³¡ªL

­ã´£³¡ªL¤j¤j¡A¤w¸g¥i¥H¨Ï¥Î¤F¡A·PÁ±z¨ó§U³B²z°ÝÃD¡AÁÂÁÂ!