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

IF...§PÂ_¥[Á`°ÝÃD

IF...§PÂ_¥[Á`°ÝÃD

¥»©«³Ì«á¥Ñ 074063 ©ó 2015-7-31 21:57 ½s¿è

½Ð°Ý¤U¦C¨ç¼Æ¦p¦ó¼g¦¨VBA

    G3=IF(C3="","",C3+F3)     ¤U©Ô½Æ»s¤½¦¡     
    G4=IF(C3-G3=0,"µ²","")    ¤U©Ô½Æ»s¤½¦¡  (®æ¦¡¤ÆÀx¦s®æ, ­YG4="µ²"   (²ÊÅéÂŦr)

½Ð¥Î¿ý»s¥¨¶°¤è¦¡¦A¨Ó­×§ï½d³ò
  1. Sub ¥¨¶°3()
  2.     Range("G1").Select
  3.     ActiveCell.FormulaR1C1 = "=IF(RC[-4]="""","""",RC[-4]+RC[-1])"
  4.     Range("G1").Select
  5.     Selection.AutoFill Destination:=Range("G1:G4"), Type:=xlFillDefault
  6.     Range("G1:G4").Select
  7. End Sub
½Æ»s¥N½X
§â½d³òG4§ïÅܼÆ
x=[C65536].end(xlup).Row
Range("G1:G" & x)
  1. Sub ¥¨¶°3()
  2. Dim x As Integer
  3.     x = [C65536].End(xlUp).Row
  4.     Range("G1").FormulaR1C1 = "=IF(RC[-4]="""","""",RC[-4]+RC[-1])"
  5.     Range("G1").AutoFill Destination:=Range("G1:G" & x), Type:=xlFillDefault
  6. End Sub
½Æ»s¥N½X

TOP

¥Î¿ý»s¤F¸Ñµ{§Ç¦A¨Ó­×§ï©Î¬Oª½±µ¨Ï¥Î
  1. Sub test()
  2. For i = 1 To [C65536].End(xlUp).Row
  3.     If Cells(i, 3) = "" Then
  4.         Cells(i, 7) = ""
  5.     Else
  6.         Cells(i, 7) = Cells(i, 3) + Cells(i, 6)
  7.         Cells(i, 7).Font.Color = vbBlue
  8.         Cells(i, 7).Font.Bold = True
  9.     End If
  10. Next
  11. End Sub
½Æ»s¥N½X

TOP

¦^´_ 3# koo


    ½Ð°ÝVBA¯à¹³¨ç¼Æ¤@¼Ë§Y®É¦Û°Ê¹Bºâ¶Ü

TOP

¦^´_ 4# 074063

VBA¬Ò¬O§Q¥Î¨Æ¥ó¨Ó±Ò°Êµ{¦¡ªº¡A©Ò¥H·íµM¥i¥H°µ§Y®É¹Bºâ¡A¥u¬O¬Ý§A¥Î¤°»ò¼Ëªº¨Æ¥ó¨Ó±Ò°Ê½}¤F¡A
§Ú¥H G3=IF(C3="","",C3+F3)     ¤U©Ô½Æ»s¤½¦¡  ³o­Ó¤½¦¡¨Ó°µ¨Ò¤l¡A¥i¥H§Q¥Î¤u§@ªí¨Æ¥ó"Àx¦s®æÅܧó¨Æ¥ó"¨Ó¹ê²{¦p¤U¦C¤¤ªºµ{¦¡
µ{¦¡»¡©ú¡G·í§A¦bCÄæ©Î«hFÄ檺²Ä3¦C°_¡A¿é¤J¼Æ¦r(ÅܧóÀx¦s®æ¤º®e)´N·|±Ò°Ê³o­Ó¨Æ¥ó¨Ó¹ê²{§Ú­Ì­nªº¥Øªº¡C
¦ý¥¦¥²¶·©ñ¦b¤u§@ªí¨Æ¥ó襤(¦p¤U¹Ï¨Ò©ñ¸mªº¦ì¸m)
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. If Target.Row > 2 And Target.Column = 6 Or Target.Column = 3 Then'
  3.     If Cells(Target.Row, 3) = "" And Cells(Target.Row, 6) = "" Then'·íCÄæ©ÎFÄæ¬ÒªÅ¥Õ®É
  4.         Cells(Target.Row, "G") = "" '«hGÄ欰ªÅ¥Õ
  5.     Else
  6.         Cells(Target.Row, "G") = Cells(Target.Row, "C") + Cells(Target.Row, "F")'§_«hCÄæ+FÄæ
  7.     End If
  8. End If
  9. End Sub
½Æ»s¥N½X
A.png
2015-8-1 23:57

TOP

¥»©«³Ì«á¥Ñ 074063 ©ó 2015-8-2 21:10 ½s¿è

¦^´_ 5# lpk187

If Cells(Target.Row, 3) = "" And Cells(Target.Row, 6) = "" Then'·íCÄæ©ÎFÄæ¬ÒªÅ¥Õ®É

½Ð°Ýtarget.row ¤£¬O¦Cªº·N«ä¶Ü @@

¦pªGFÄæ¤]¬O¤½¦¡±a¥X­Èªº¸Ü, GÄæ¦n¹³´N¨S¦³§@¥Î

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2015-8-2 22:09 ½s¿è

¦^´_ 6# 074063


    target.row ¬O¦C¨S¿ù¡A¦ý§A©¿²¤¤F"3"¬OCÄæ¡I6¬OFÄæ¡C=>Cells( target.Row, target.Column)target.Column¬O3©Î6
target¬O§@¥ÎÀx¦s®æ¡A°²³]§A¦bC3Áä¤J¼Æ­È®É¡Atarget´N¬O¨º´N§@¥ÎªºÀx¦s®æ¡AVBA­Y¥Îµ{¦¡­pºâªº¸Ü¨Ã¤£·|¦³¤½¦¡Åã¥Ü¦bÀx¦s®æ¤¤(G3)¡A¦Ó¬O·|ª½±µÅã¥Ü­pºâ¹L«áªº­È

¦pªGFÄæ¤]¬O¤½¦¡±a¥X­Èªº¸Ü, GÄæ¦n¹³´N¨S¦³§@¥Î
Ex:1.FÄæ¬O¨S¦³¤½¦¡ªº¡A¨º¥u¬O¥Îµ{¦¡§PÂ_¬O§_¬°ªÅ¥Õ¡A
      2.GÄæ­Y¨S§@¥Î¥i¯à¬O§A¨S«ö·Ó§Ú¹Ï©Ò¥Ü©ñ¦b¸Ó©ñªº¦a¤è¡I

¤U­±¬O¦^µª1¼Óªº°ÝÃD
¥t¥~VBA¬O¥Î¨ÓÀ°§A¦Û°Ê¤Æ©ÎªÌ¬OÀ°§A°õ¦æ½ÆÂø¤u§@¡A­Y ¥Î¨Ó¼g¨ç¼Æ¡A°£«D¦bÀx¦s®æ¤¤ªº¨ç¼Æ·|¦]¬YºØ¦]¯ÀÅܤƮɡA§_«h¬O¨S¦³·N¸qªº¡I
¦p¤U¹Ïªº¿ï¶µ >¤½¦¡¤¤ªº¬õ®Ø¤¤¬°¤°»ò·|·|¦³¬¡­¶Ã¯­pºâªº¿ï¶µ¡A³o¬O¬°¤F·í¦b¤u§@ªí¤¤¨ç¼Æ«Ü¦h«Ü¦hªº±¡ªp¤UÅý§A¿ï¾Ü¬O¤£¬O­n¦Û°Ê©ÎªÌ¤â°Ê¡A¦ýVBA«o¨S¦³³o­Ó°ÝÃD¥¦¥uÀ°§A°µµ{¦¡ªº¬yµ{¦Ó¤w¡A¦Ó¤£·|¥þ³¡¨«¤@¦¸¡I
A.png
2015-8-2 22:04

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2015-8-2 22:31 ½s¿è

¦^´_ 6# 074063


    §Ú¤W¤@¼Óªº¦^µª¦n¹³¦³¨Ç»~·|§Aªº·N«ä¡I
¥HIf Cells(Target.Row, 3) = "" And Cells(Target.Row, 6) = "" Then'·íCÄæ©ÎFÄæ¬ÒªÅ¥Õ®É
...
¦pªGFÄæ¤]¬O¤½¦¡±a¥X­Èªº¸Ü, GÄæ¦n¹³´N¨S¦³§@¥Î
¨Ó»¡
­Y¬OFÄ榳¤½¦¡ªº¸Ü¡AAnd Cells(Target.Row, 6) = "" ¬O¥i¥H§R°£ªº¡I

·íµ{¦¡³]­p®v¦b¼gµ{¦¡®É¡A¤j³¡¥÷³£·|¨Ì·Ó¾ã­Ó¤u§@ªí©ÎªÌÂX¤j¨ì¾ã­Ó¬¡­¶Ã¯¨Ó°µ¦Ò¶q¡A¦Ó¤£·|¥u¥Î³æ¤@¨ç¼Æ´N·í¦¨°ò¦¡A¦Ò¼{ªº¤è¦¡¤£¦P¡A°õ¦æ«á¤]·|¦]¦Ò¼{ªº¤£¦P¦Ó¦³¤£¦Pªºµ²ªG¡C
´N¹³³o­Ó»~¸Ñ¤@¼Ë¡A§Ú¦b¼g³oµ{¦¡®É·Q¨ìG3=IF(C3="","",C3+F3)¡AC3¬OªÅ¥Õ©Î«DªÅ¥Õ¡A¦Ó¤£ª¾¹DF3¦³¨S¦³¤½¦¡¡A¦ý§Ú¬°¤F¸É¨¬¦Ò¼{ªº¤£©P¦Ó¼g¤UAnd Cells(Target.Row, 6) = ""
©Ò¥H³Ì«á«Øij§A­Y¦³°ÝÃD®É¥i¥H¤W¶Ç§Aªº³¡¥÷ÀɮרӡA¦nÅý­n¸Ñµªªº¤H¦³©Ò¦]À³¡I¤]·|§ó©P¥þªº¡I

TOP

¦^´_ 8# lpk187


    ·PÁ¤j¤j¸Ñµª, ³o­Ó³¡¥÷§Ú¦A½m²ß¤@¤U :D

TOP

¥»©«³Ì«á¥Ñ 074063 ©ó 2015-8-3 00:09 ½s¿è

Book1x.rar (8.96 KB) ¦^´_ 8# lpk187


    ªþÀɵ¹¤j¤j°Ñ¦Ò, ¥t¸ß°Ý­Y¸ê®Æ¦h®É, ¨Ï¥Î¨ç¼Æ¤]¦h³t«×·|ÅܺC...¥ÎVBA³B²z·|¤£·|¤ñ¸û¦n

TOP

        ÀR«ä¦Û¦b : ¤£­n¤p¬Ý¦Û¤v¡A¦]¬°¤H¦³µL­­ªº¥i¯à¡C
ªð¦^¦Cªí ¤W¤@¥DÃD