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

°j°é¯Ó®Éªº°ÝÃD

°j°é¯Ó®Éªº°ÝÃD

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2019-8-22 01:26 ½s¿è

´ú¸ÕÀÉ¡J °j°é°ÝÃD.rar (104.1 KB)

·Q±N"«e¤T¤p¼Æ"¤u§@ªíªº.Range("AP2:AP127")½d³ò¤ºªº"¤j"§ï¬°"¤p"¡F
¨Ã±N44¸¹©³¦â§ï¬°38¸¹©³¦â¡A4¸¹©³¦â§ï¬°40¸¹©³¦â¡A8¸¹©³¦â§ï¬°39¸¹©³¦â¡C
¥t±N.Range("AX2:BB127")½d³ò¤ºªº"¤j"§ï¬°"¤p"¡F
³Ì«á±N[AP2:BC127].Copy [BE2]
¥Ø«eªºµ{¦¡½X¦p¤U¡J
'§ó§ï¤å¦r©M©³¦â  '=>¦C247
    For Each Rng In .Range("AP2:AP127")
        Rng.Value = Replace(Rng, "¤j", "¤p")
        i = Rng.Interior.ColorIndex
        Rng.Interior.ColorIndex = IIf(i = 44, 38, IIf(i = 4, 40, IIf(i = 8, 39, i)))
    Next
    For Each Rng In .Range("AX2:BB127")
        Rng.Value = Replace(Rng, "¤j", "¤p")
    Next
    [AP2:BC127].Copy [BE2]   '=>¦C256

°ÝÃD¡J¤W­zªº°Ï¬qµ{¦¡½X¶]ªº®É¶¡²§±`(¬ù¶·¯Ó®É15¬í)¡C
»Ý¨D¡J½Ð°Ý­n¦p¦ó¥¿½T½s¼g¡H¥H§Q´£¤É°õ¦æªº®Ä²v¡C

PS¡Jªþ¤W2­Ó.CSV´ú¸ÕÀÉ
¸Û½Ð¦U¦ì¤j¤j¤£§[À°¦£¡I ÁÂÁ¡I

ª¦¤å³\¤[¡AÁÙ¬O§ä¤£¨ì¾A¥Îªº»yªk^^"
¥ý¥H¿ý»sªº»yªk¼È¥Î~~¦pªG¥i¥H¦A­×´îªº¸Ü¡A¸Û½Ð¦U¦ì¤j¤j¤£§[½ç±Ð!ÁÂÁÂ!
'§ó§ï¤å¦r©M©³¦â
    Range("AP2:AP127").Select
    Application.FindFormat.Interior.ColorIndex = 44
    Application.ReplaceFormat.Interior.ColorIndex = 38
    Selection.Replace What:="¤j", Replacement:="¤p", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
        ReplaceFormat:=True
    Application.FindFormat.Interior.ColorIndex = 4
    Application.ReplaceFormat.Interior.ColorIndex = 40
    Selection.Replace What:="¤j", Replacement:="¤p", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
        ReplaceFormat:=True
    Application.FindFormat.Interior.ColorIndex = 8
    Application.ReplaceFormat.Interior.ColorIndex = 39
    Selection.Replace What:="¤j", Replacement:="¤p", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
        ReplaceFormat:=True
   
    Range("AX2:BB127").Select
    Selection.Replace What:="¤j", Replacement:="¤p", LookAt:=xlPart

TOP

°j°é¥»¨­¦¸¼Æ¦h¡A¦³®É¤]·|ºC¡A¤×¨ä¬O¹ï¶H¬OÀx¦s®æ¡C
¦³¨Çª¬ªp¥i¥H¤£¥Î°j°é¡Aª½±µ¹ï¾ã­Ó½d³ò´M§ä¨ú¥N¡AÀ³¸Ó·|¤ñ¸û§Ö¡C
IIf¥»¨­¤ñ¸ûºC¡A¦³®É­Ôµ{¦¡½X¸û²²¤¡A¬Û¹ï°õ¦æ®Ä²v¤Ï¦Ó¸û§C¡A§âµ{¦¡½X¼gªø¤@ÂI¡A
Rng.Interior.ColorIndex = IIf(i = 44, 38, IIf(i = 4, 40, IIf(i = 8, 39, i)))
¦¹¦æ®i¶}¡G
if .....then
elseif ...... then
else
end if
©Î¥ÎSelect Case»y¥y¤]¦æ¡A
³o¼Ëªº«¬¦¡®Ä²vÀ³¸Ó¤ñ¸û¦n¡C
Word§e²{·¥­PªºÀRºA¤§¬ü¡AExcel®i²{·¥­Pªº°ÊºA¤§¬ü¡C

TOP

¦^´_ 3# linyancheng
ÁÂÁª©¥D«ü±Ð!§Ú¸Õ¸Õ¬Ý^^

TOP

¡u¨ú¥N¡v¤£­n¥Î°j°é,·|¬Ù«Ü¦h®É¶¡
Range("AP2:AP127").Replace What:="¤j", Replacement:="¤p"
Range("AX2:BB127").Replace What:="¤j", Replacement:="¤p"

TOP

¦^´_ 5# mistery
ÁÂÁ¤j¤jªº«ü±Ð¡C

¥i§_¦A½Ð°Ý : ©³¦â¨ú¥N­n«ç»ò¼g¤~¥¿½T?
ÁÂÁ±z^^

TOP

Dim Ur(3) As Range, Cx%, Cv%, Crr
Crr = Array(0, 38, 40, 39)
.Range("AP2:AP127").Replace "¤j", "¤p"
For Each Rng In .Range("AP2:AP127")
    Cx = Rng.Interior.ColorIndex
    Cv = Switch(Cx = 44, 1, Cx = 4, 2, Cx = 8, 3, Cx = Cx, 0)
    If Ur(Cv) Is Nothing Then Set Ur(Cv) = Rng Else Set Ur(Cv) = Union(Ur(Cv), Rng)
    For i = 1 To 3
        If Not Ur(Cv) Is Nothing Then Ur(Cv).Interior.ColorIndex = Crr(i)
    Next i
Next
.Range("AX2:BB127").Replace "¤p", "¤j"
[AP2:BC127].Copy [BE2]

¬O§_¦³§Ö¤@¨Ç, ¨S¿ìªk´ú, ¸Õ¸Õ§a~~


=================================

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2019-8-29 03:03 ½s¿è

¦^´_ 7# ­ã´£³¡ªL
·PÁª©¥Dªº¼ö¤ß¬Û§U¡C
´ú¸Õµ²ªG : ¨S¦³¨ü¨ì  For Each Rng In .Range("AP2:AP127")...... Next  ªº°j°é¼vÅT¡A§Ö«Ü¦h¡C
¸U¤À·PÁÂ^^

TOP

¦^´_ 8# ziv976688

§ó¥¿:
Dim Ur(3) As Range, Cx%, Cv%, Crr
Crr = Array(0, 38, 40, 39)
.Range("AP2:AP127").Replace "¤j", "¤p"
For Each Rng In .Range("AP2:AP127")
    Cx = Rng.Interior.ColorIndex
    Cv = Switch(Cx = 44, 1, Cx = 4, 2, Cx = 8, 3, Cx = Cx, 0)
    If Ur(Cv) Is Nothing Then Set Ur(Cv) = Rng Else Set Ur(Cv) = Union(Ur(Cv), Rng)
Next
For i = 1 To 3  '³o¤@¬q­n±q¤W¤è°j°é§ì¥X¨Ó
     If Not Ur(Cv) Is Nothing Then Ur(Cv).Interior.ColorIndex = Crr(i)
Next i
.Range("AX2:BB127").Replace "¤p", "¤j"
[AP2:BC127].Copy [BE2]

TOP

¦^´_ 9# ­ã´£³¡ªL
¦A¦¸¸U¤À·PÁª©¥Dªº¼ö¤ßÀ°¦£^^(Áù°`)

TOP

        ÀR«ä¦Û¦b : ¦h°µ¦h±o¡C¤Ö°µ¦h¥¢¡C
ªð¦^¦Cªí ¤W¤@¥DÃD