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

½ÐÀ°À°¦£VBA²¤Æ¤@¤UÐù!!

½ÐÀ°À°¦£VBA²¤Æ¤@¤UÐù!!

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-7-27 07:16 ½s¿è

½Ð¦U¦ì¤j¤j¯àÀ°¦£Â²¤Æ¤@¤U¤U­±ªºµ{¦¡¡A¤£³Ó·P¿E°Ú!!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Range("D4") = "=IF(C4="""","""",VLOOKUP(¤é³øªí1!C4,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D5") = "=IF(C5="""","""",VLOOKUP(¤é³øªí1!C5,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D6") = "=IF(C6="""","""",VLOOKUP(¤é³øªí1!C6,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D7") = "=IF(C7="""","""",VLOOKUP(¤é³øªí1!C7,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D8") = "=IF(C8="""","""",VLOOKUP(¤é³øªí1!C8,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D9") = "=IF(C9="""","""",VLOOKUP(¤é³øªí1!C9,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D10") = "=IF(C10="""","""",VLOOKUP(¤é³øªí1!C10,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D11") = "=IF(C11="""","""",VLOOKUP(¤é³øªí1!C11,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D12") = "=IF(C12="""","""",VLOOKUP(¤é³øªí1!C12,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D13") = "=IF(C13="""","""",VLOOKUP(¤é³øªí1!C13,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D14") = "=IF(C14="""","""",VLOOKUP(¤é³øªí1!C14,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D15") = "=IF(C15="""","""",VLOOKUP(¤é³øªí1!C15,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D16") = "=IF(C16="""","""",VLOOKUP(¤é³øªí1!C16,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D17") = "=IF(C17="""","""",VLOOKUP(¤é³øªí1!C17,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D18") = "=IF(C18="""","""",VLOOKUP(¤é³øªí1!C18,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D19") = "=IF(C19="""","""",VLOOKUP(¤é³øªí1!C19,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D20") = "=IF(C20="""","""",VLOOKUP(¤é³øªí1!C20,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D21") = "=IF(C21="""","""",VLOOKUP(¤é³øªí1!C21,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D22") = "=IF(C22="""","""",VLOOKUP(¤é³øªí1!C22,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D23") = "=IF(C23="""","""",VLOOKUP(¤é³øªí1!C23,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D24") = "=IF(C24="""","""",VLOOKUP(¤é³øªí1!C24,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D25") = "=IF(C25="""","""",VLOOKUP(¤é³øªí1!C25,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D26") = "=IF(C26="""","""",VLOOKUP(¤é³øªí1!C26,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D27") = "=IF(C27="""","""",VLOOKUP(¤é³øªí1!C27,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D28") = "=IF(C28="""","""",VLOOKUP(¤é³øªí1!C28,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D29") = "=IF(C29="""","""",VLOOKUP(¤é³øªí1!C29,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D30") = "=IF(C30="""","""",VLOOKUP(¤é³øªí1!C30,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D31") = "=IF(C31="""","""",VLOOKUP(¤é³øªí1!C31,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D32") = "=IF(C32="""","""",VLOOKUP(¤é³øªí1!C32,¸ê®Æ®w!$A$1:$B$66,2))"
   Range("D33") = "=IF(C33="""","""",VLOOKUP(¤é³øªí1!C33,¸ê®Æ®w!$A$1:$B$66,2))"

   If [c4] <> "" And [a2] = "" Then [a2] = Date
   If [c4] = "" Then [a2] = ""

   If [c4] <> "" And [b4] = "" Then [b4] = Date
   If [c4] = "" Then [b4] = ""
   End Sub
§A²q!!

§ó¥¿  ¤U­±ªºIF¤½¦¡¬O³o³¡¤À­n²¤Æ¡A¤U­±ªº¤½¦¡­n±qC4¤@ª½­«½Æ«Ü¦h¡A«ô°UÀ°À°¦£¡A·P®¦

   If [c4] <> "" And [b4] = "" Then [b4] = Date
   If [c4] = "" Then [b4] = ""
   If [c5] <> "" And [b5] = "" Then [b5] = Date
   If [c5] = "" Then [b5] = ""
§A²q!!

TOP

¾Ç²ßvba³Ì°ò¥»ªº­n¾Ç²ß«Ø¥ß°j°é¡A¦p¦¹¨Ò¥i¥H³o¼Ë¡G
For i = 4 To 100
If Range("c" & i) = "" Then
Range("b" & i) = ""
Else
If Range("b" & i) = "" Then Range("b" & i) = Date
End If
Next

TOP

¦^´_ 1# bear0925900003
¥ÎR1C1ªí¥Üªk
  1. Range("D4:D33").FormulaR1C1 = "=IF(RC[-1]="""","""",VLOOKUP(¤é³øªí1!RC[-1],¸ê®Æ®w!R1C1:R2C66,2))"
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦U¦ì¤j¤j¤Ó¯«¤F¡A·P¿Eªü
§A²q!!

TOP

¹ï¤£°_!!¤p§Ì¬Ý®Ñ¦Û¾Ç¡A"¨±°j"³o½g©l²×µLªk°Ñ³z.....
§A²q!!

TOP

¦^´_ 4# GBKEE

¤£¯à¥Î­C
§A²q!!

TOP

¦^´_ 7# bear0925900003
§A»¡:¤£¯à¥Î­C §Ú¤]¤£ª¾¹D,§A­n»¡²M·¡¿ù»~­È¬O¦ó?,¥iªþÀɬݬÝ
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

Range("D433").FormulaR1C1 = "=IF(RC[-1]="""","""",VLOOKUP(¤é³øªí1!RC[-1],¸ê®Æ®w!R1C1:R2C66,2))"

³o­Ó¤½¦¡²z½×¤WÀ³¸ÓD4~D33³£¯à¨ã¦³¬Û¦Pªº¥\¯à¡A¦ý¸ÕÅç«á¥u¦³D4¡BD5¥i¥H§ä¨ì¥¿½T¸ê°T¡AD5¤§«á³£¨Sªk¥¿½T¹ï¨ì¬Û¹ï¸ê°T
§A²q!!

TOP

¦^´_ 9# bear0925900003
½Ð¦A¸Ô¬Ý ¤u§@ªí¨ç¼Æ VLOOKUPªº¥Îªk»¡©ú
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ­ì½Ì§O¤H´N¬Oµ½«Ý¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD