[µo°Ý] ¶K¤J­ÈVBAºÃ°Ý

yc1031

VBA CODEªº­ì©l¨Ó·½ : https://alice57.pixnet.net/blog/post/23273559

¤j®a¦n, §Ú¦bºô¸ô§ä¨ì­Ó¶K¤J­ÈªºVBA, ­×§ï«á, ¦³´X­Ó¦a¤è¤£©ú¥Õ, ¦A½Ð±Ð¤@¤U¦U¦ì

1. µµ¦â¦r¸Ìªº"0 + 16"  ³o­Ó¬O¤°»ò·N«ä©O?
2. ¦bCOPY¨ìdSht , ­ì©lªºCODE¬O".Range("B2:B" & y).Copy MySht.[C2]", ¦ý¶K¨ìCÄæ®É, ¸ÓÄæ¦ìªº®æ½u³£·|³Q§R±¼,  ³o­Ó­n«ç»ò§ï©O?
* §Ú¬O§ï¥Î
dSht.Range("B2:B" & y).Copy
MySht.Range("C2:J1000").PasteSpecial Paste:=xlPasteValues
¦³¦¨¥\, ¥u¬O·Qª¾¹D¦b­ì©lªº.Range("B2:B" & y).Copy MySht.[C2]       <---- ¦b³o­ÓCODE¤U­n«ç»ò¼g, ¥u¶K¤J­È´N¦n, ¤£­n°Ê¨ì®æ¦¡

·PÁ¦U¦ì!


Sub ¶KÂd¸¹()
Dim MySht As Worksheet, dSht As Worksheet, y&
  Set MySht = Sheets("¥X³f«á")
  Set dSht = Sheets("¥X³f©ú²Ó")
  With dSht
  MySht.Range("C2:C10000").Clear   '²M°£­ì¦³¸ê®Æ
  dSht.AutoFilterMode = False    '¸Ñ°£¿z¿ï¼Ò¦¡
  y = dSht.Range("B65536").End(xlUp).Row
  If y < 2 Then MsgBox "¡°¨Ó·½ªí¡e¢ÐÄæ¡fµL¸ê®Æ¡I¡@", 0 + 16: Exit Sub
  '¡ô¥ý¸Ñ°£¿z¿ï¡A¦AÀË´ú¸ê®Æ§À¦C¦C¸¹¡A­Y¿z¿ï¤¤¡AÀË´ú¦C¸¹·|¦³»~®t
  If WorksheetFunction.CountIf(dSht.Range("A2:A" & y), "1") = 0 Then
      MsgBox "¡°¨Ó·½ªí¡eAÄæ¡fµL¡e1¡f¿z¿ï­È¡I¡@", 0 + 16: Exit Sub
  End If

  '¡ôÀË´ú¡e1¡f¼Æ
  'ª`·N¡G"~*"¤~¯à³æ¿ï¡e*¡f¡A§_«h¨ä¥¦¤å¦r¡]¨Ò¦p¡GA.B.C¡^¤]·|¿z¿ï¶i¨Ó

      dSht.Range("$A$1:$AB$1500").AutoFilter Field:=1, Criteria1:="1"
      dSht.Range("B2:B" & y).Copy
      MySht.Range("C2:J1000").PasteSpecial Paste:=xlPasteValues
     '­ì©lªºCODE¬O".Range("B2:B" & y).Copy MySht.[C2]", ¦ý¶K¨ìCÄæ®É, ¸ÓÄæ¦ìªº®æ½u³£·|³Q§R±¼

      dSht.AutoFilterMode = False '¸Ñ°£¿z¿ï¼Ò¦¡
      MySht.AutoFilterMode = False '¸Ñ°£¿z¿ï¼Ò¦¡
  End With

n7822123

¦^´_ 1# yc1031


»Ý¨D1:
1. µµ¦â¦r¸Ìªº"0 + 16"  ³o­Ó¬O¤°»ò·N«ä©O?

Msgbox ¥Îªkªº Button°Ñ¼Æ­È~~~ 16 = "Åã¥Ü Critical Message ¹Ï¥Ü" (0¥i¥H¬Ù²¤)

½Ð°Ñ¦Ò¤U¹Ï·L³n»¡©ú

¤U¸ü (23.66 KB)

2020-6-19 01:49

¥i¥H²V¦X·f°t¤£¦P§Î¦¡¡A¤ñ¦p»¡1+16+256+16384

256 ¥Nªí²Ä2­Ó«ö¶s¬O¹w³]­È¡Aª½±µ«ö¤UEnter¡A´N¬O«ö¤U²Ä2­Ó«ö¶s(¥H¦¹¨Ò¨Ó»¡·|¬O"¨ú®ø")

¥H¤Uµ{¦¡§A¥i¥H°õ¦æ¬Ý¬Ý¡A´N·|À´¤F

¦@¦³16­Ó¤£¦PªºMsgbox µøµ¡


Sub PlayMsgbox()
Arr = Array(1, 2, 3, 4)
Brr = Array(16, 32, 48, 64)
For A = 0 To UBound(Arr): For B = 0 To UBound(Brr)
  n = n + 1
  MsgBox "Test-" & n, Arr(A) + Brr(B)
Next: Next
End Sub

¤]³\¤U­±ªºµ{¦¡§A¤ñ¸û¬Ý±oÀ´¡A¨âªÌ¬O¤@¼Ëªº

Sub PlayMsgbox2()
n = n + 1: MsgBox "Test-" & n, 1 + 16
n = n + 1: MsgBox "Test-" & n, 2 + 16
n = n + 1: MsgBox "Test-" & n, 3 + 16
n = n + 1: MsgBox "Test-" & n, 4 + 16

n = n + 1: MsgBox "Test-" & n, 1 + 32
n = n + 1: MsgBox "Test-" & n, 2 + 32
n = n + 1: MsgBox "Test-" & n, 3 + 32
n = n + 1: MsgBox "Test-" & n, 4 + 32

n = n + 1: MsgBox "Test-" & n, 1 + 48
n = n + 1: MsgBox "Test-" & n, 2 + 48
n = n + 1: MsgBox "Test-" & n, 3 + 48
n = n + 1: MsgBox "Test-" & n, 4 + 48

n = n + 1: MsgBox "Test-" & n, 1 + 64
n = n + 1: MsgBox "Test-" & n, 2 + 64
n = n + 1: MsgBox "Test-" & n, 3 + 64
n = n + 1: MsgBox "Test-" & n, 4 + 64
End Sub


»Ý¨D2:
2. ¦bCOPY¨ìdSht , ­ì©lªºCODE¬O".Range("B2:B" & y).Copy MySht.[C2]", ¦ý¶K¨ìCÄæ®É, ¸ÓÄæ¦ìªº®æ½u³£·|³Q§R±¼,  ³o­Ó­n«ç»ò§ï©O?
* §Ú¬O§ï¥Î
dSht.Range("B2:B" & y).Copy
MySht.Range("C2:J1000").PasteSpecial Paste:=xlPasteValues
¦³¦¨¥\, ¥u¬O·Qª¾¹D¦b­ì©lªº.Range("B2:B" & y).Copy MySht.[C2]       <---- ¦b³o­ÓCODE¤U­n«ç»ò¼g, ¥u¶K¤J­È´N¦n, ¤£­n°Ê¨ì®æ¦¡

¥i¥H§ï¦¨

MySht.[C2].Resize(y - 1) =.Range("B2:B" & y).Value