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

´M§ä&¨ú¥N

´M§ä&¨ú¥N

¦U¦ì:¥H¤U§Ú¥Î¿ý¨îªº¥¨¶°,½Ð°Ý¦p¦ó²³æ¤@ÂI,ÁÂÁÂ.

Sub ¨ú¥N()
    ActiveSheet.UsedRange

    Selection.Replace What:="700", Replacement:="J07", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="1500", Replacement:="J15", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="1100", Replacement:="J11", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="2300", Replacement:="J23", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
  
End Sub

¥»©«³Ì«á¥Ñ register313 ©ó 2012-4-27 20:18 ½s¿è

¦^´_ 1# mmggmm
  1. Sub ¨ú¥N()
  2. Set Rng = ActiveSheet.UsedRange
  3. S = Array(700, 1100, 1500, 2300)
  4. R = Array("J07", "J11", "J15", "J23")
  5. For I = 0 To UBound(S)
  6.   Rng.Replace S(I), R(I)
  7. Next I
  8. End Sub
½Æ»s¥N½X
  1. Sub ¨ú¥N()
  2. Set Rng = ActiveSheet.UsedRange
  3. S = Array(700, 1100, 1500, 2300)
  4. For I = 0 To UBound(S)
  5.   Rng.Replace S(I), Application.Text(S(I) / 100, "J00")
  6. Next I
  7. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# register313
½Ð°Ý±z¬°¦ó¥Î Rng.Replace S(I), Application.Text(S(I) / 100, "J00") ªº¤è¦¡³B²z¡H
¥¦»P¤U¦C³B²z¤è¦¡¡A®t§O¦b©ó¤@­Ó¥H°}¦C¡B¤@­Ó¥Hª½±µ´À´«ªº¤è¦¡°µ²§°Ê¡C
R = Array("J07", "J11", "J15", "J23")
Rng.Replace S(I), R(I)
¦ý§Ú¦n©_ªº¬O Application.Text(S(I) / 100, "J00") ¬O¦p¦ó¹B§@ªº¡H
ÁÂÁ±z¡I

TOP

¦^´_ 3# c_c_lai

         Application.Text(S(I) / 100, "J00")   ³]©wS(I) / 100¤§®æ¦¡¬°J00
¥ç¥i¥Î         Format(S(I) / 100, "J00")   ³]©wS(I) / 100¤§®æ¦¡¬°J00

I=0®É,S(I)=700,Format(S(I) / 100, "J00") =Format(7, "J00") =J07
I=1®É,S(I)=1100,Format(S(I) / 100, "J00") =Format(11, "J00") =J11
I=2®É,S(I)=1500,Format(S(I) / 100, "J00") =Format(15, "J00") =J15
I=3®É,S(I)=2300,Format(S(I) / 100, "J00") =Format(23, "J00") =J23

TOP

¦^´_ 4# register313
§Ú¥Î¤F     Range("D5").Value = Format(11, "J00") ¨Ó°µ´ú¸Õ¡A³ºµMµª®×·|¬O J11¡A
Amazing!  
Thanks a lot.

TOP

­ì¿ý¨î¥¨¶°¥u¨ú¥N¿ï¾Ü½d³ò¤ºªº¸ê®Æ
¦Ó°õ¤j¤j´£¨Ñªº«á«h¨ú¥N¤F¥þ³¡¸ê®Æ,¤£ª¾¨º¸Ì¦³°ÝÃD.

TOP

¦^´_ 6# mmggmm
  1. Sub ¨ú¥N()
  2. Set Rng = Selection
  3. S = Array(700, 1100, 1500, 2300)
  4. R = Array("J07", "J11", "J15", "J23")
  5. For I = 0 To UBound(S)
  6.   Rng.Replace S(I), R(I)
  7. Next I
  8. End Sub
½Æ»s¥N½X
©Î
  1. Sub ¨ú¥N()
  2. Set Rng = Selection
  3. S = Array(700, 1100, 1500, 2300)
  4. For I = 0 To UBound(S)
  5.   Rng.Replace S(I), Application.Text(S(I) / 100, "J00")
  6. Next I
  7. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-4-29 14:48 ½s¿è

¦^´_ 6# mmggmm
Rng.Replace S(I), R(I)    ¨S«ü©ú LookAt := xlWhole ©Î LookAt :=  xlPart
·|¨Ì¤W¦¸ªº LookAt  
vba ªº »¡©ú
Replace ¤èªk
Range ª«¥ó®É¥Î Replace ¤èªk¡C
¶Ç¦^ Boolean ­È¡A¦¹­Èªí¥Ü«ü©w½d³ò¤ºÀx¦s®æ¤¤ªº¦r¤¸¡C¨Ï¥Î¦¹¤èªk¤£·|Åܧó¿ï©w½d³ò©M¥Ø«eÀx¦s®æ¡C
expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat)
¥H¤U¬O°Ñ¼Æªº»¡©ú
What     ¥²¿ïªº Variant¡C±z­n Microsoft Excel ´M§äªº¦r¦ê¡C
Replacement     ¥²¿ïªº Variant¡C­n¸m´«ªº¦r¦ê¡C
LookAt      ¿ï¾Ü©Êªº Variant¡C¥i¬°¤U¦C XlLookAt ±`¼Æ¤§¤@¡GxlWhole ©Î xlPart¡C
SearchOrder      ¿ï¾Ü©Êªº Variant¡C¥i¬°¤U¦C XlSearchOrder ±`¼Æ¤§¤@¡GxlByRows ©Î xlByColumns¡C
MatchCase     ¿ï¾Ü©Êªº Variant¡C­Y«ü©w¬° True¡A«h·j´M®É¤j¤p¼gµø¬°¬Û²§¡C
MatchByte     ¿ï¾Ü©Êªº Variant¡C¦pªG¦b Microsoft Excel ¤¤¿ï¾Ü©Î¦w¸Ë¤G¦ì¤¸»y¨¥¤ä´©¡A±z¶È¥i¥H¨Ï¥Î¦¹¤Þ¼Æ¡C­Y«ü©w¬° True¡A´N¨ÏÂù¦ì¤¸²Õ¦r¤¸¶È²Å¦XÂù¦ì¤¸²Õ¦r¤¸¡C­Y«ü©w¬° False¡A«hÂù¦ì¤¸²Õ¦r¤¸¥i²Å¦X¨äµ¥»ùªº³æ¦ì¤¸²Õ¦r¤¸¡C
SearchFormat     ¿ï¾Ü©Êªº Variant¡C¤èªkªº·j´M®æ¦¡¡C
ReplaceFormat     ¿ï¾Ü©Êªº Variant¡C¤èªkªº¨ú¥N®æ¦¡¡C
§A¥Î¿ý»s¥i¬d¬Ý °Ñ¼ÆªºÅܤÆ
LookAt := xlWhole  ¦p¹Ï ©Ò¤Ä¿ïªº ¿ï¶µ
LookAt :=  xlPart      ¹Ï¤¤ ©Ò¤Ä¿ïªº ¿ï¶µ·|¨ú®ø

TOP

ÁÂÁ¦U¦ìªº¸Ô²Ó»¡©ú

TOP

        ÀR«ä¦Û¦b : ¨ü¤HÂI¤ô¤§®¦¡A¶··í´é¬u¥H³ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD