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

[µo°Ý] ½Ð±ÐVBA ¨â¦æÀx¦s®æªº¦r¦ê¦X¨Ö°ÝÃD~~~~~~~~~~

[µo°Ý] ½Ð±ÐVBA ¨â¦æÀx¦s®æªº¦r¦ê¦X¨Ö°ÝÃD~~~~~~~~~~

°Ý°ÝÃD101.11.20.rar (9.78 KB) :(
Sub TEST()
    ' Worksheets("Sheet1").Range("A1:A65530") = Worksheets("Sheet1").Range("B1:B65530") & Worksheets("Sheet1").Range("C1:C65530") '«¬ºA¤£²Å¦X
   
    all_number = ThisWorkbook.Sheets("Sheet1").Range("B65536").End(xlUp).Row
    For I = 2 To all_number
      Cells(I, 1) = Trim(Cells(I, 2)) & Trim(Cells(I, 3))
    Next I
End Sub

¦³¨S¦³¿ëªk¤£¶]°j°é¡A¤@­Ó­Ó²Õ¦X¦r¦ê¡A
¥i¦³¤èªkÃþ¦üRange  ¤j½d³òªº²Õ¦X¦r¦ê¡AÁÂÁÂ~~~«ü±Ð

¦^´_ 1# ji12345678
¤£¥Î°j°é..
  1. Sub TEST()
  2.     With Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Range("B65536").End(xlUp).Row)
  3.         .FormulaR1C1 = "=trim(RC[1])&trim(RC[2])"
  4.         .Value = .Value
  5.     End With
  6. End Sub
½Æ»s¥N½X

TOP

½Ð±Ð¤@¤U!  
¨Ì¤W¨Ò ­Y¬O­n¦b²Õ¦X¦r¦êªº¤¤¶¡¥[¤J¤@­Ó´î¸¹"-" , µ{¦¡­n«ç¼Ë­×§ï ?
¦p¤U:
1-¾ô¥»
2-°ª¤s
3-©£¥Ð

TOP

¦^´_ 3# ABK


.FormulaR1C1 = "=trim(RC[1])&""-""&trim(RC[2])"

TOP

¦^´_ 4# ­ã´£³¡ªL


ÁÂÁ­㴣¤j !   OK ¤F!

TOP

§Ú·Qªu¥Î³o­Óµ{¦¡¦bAÄæ¶ñ¤J¤½¦¡ , ¥Î¨Ó­pºâBÄ檺­È¬O²Ä´X¦¸­«½Æ, ¦ý¬O¦³¿ù»~, ½Ð±Ð¤j¤j­Ì­n¦p¦ó­×¥¿
   ©Î¬O¤£¶ñ¤J¤½¦¡, ¥i¥H¥Îµ{¦¡ ª½±µºâ¥X­«½Æ²Ä´X¦¸§ó¦n¡C

Sub sheet1_Formula()

With Sheets("sheet1").Range("A2:A" & Sheets("sheet1").Range("B65536").End(xlUp).Row)
        .FormulaR1C1 = "=COUNTIF($B$2:$B" & ROW() & ",B" & Row())-1"   '¶ñ¤J¤½¦¡

        .Value = .Value
    End With

End Sub

count.jpg (35.6 KB)

count.jpg

Count.zip (14.66 KB)

TOP

¦^´_ 6# ABK


    §ó¥¿:  ¤W­±ªºµ{¦¡¬O¥Î¨Ó­pºâ­«½Æªº¦¸¼Æ, ¤£¬O¶ñ¤J¤½¦¡¡C

TOP

¦^´_ 7# ABK


³o­Ó¤èªk "FormulaR1C1"  ¬OR1C1ªí¥Üªkªº¤½¦¡

§A­ì¥»ªº¤½¦¡¼gªk¤ñ¸û¹³¤@¯ëªº¤½¦¡¡A½Ð¥Î "Formula"

Excel «ÜÁo©úªº¡A¥u­n¥ÎVBA¦b³sÄò½d³ò¶ñ¤J ½d³ò¤º²Ä¤@®æ¤½¦¡´N¦n¤F~~

¨ä¥¦®æ¤½¦¡¬Û·í©ó¥k©Ô¤U©Ô~

¥H¦¹½d¨Ò¡A¥u­n¦b½d³ò¤º¶ñ¤J A2»Ý­nªº¤½¦¡§Y¥i...¤£¥Î¦A¼g Row()³oºØ¨ç¼Æ

¥ý¶ñ¤J¤½¦¡¦AÂà­È ¼gªk¦p¤U

Sub sheet1_Formula()
Rn& = [sheet1!B65536].End(3).Row
With [sheet1!A2].Resize(Rn - 1)
        .Formula = "=COUNTIF($B$2:$B2,B2)-1"    '¶ñ¤JA2ªº¤½¦¡
        .Value = .Value
End With
End Sub


¤£¥ÎExcel¤u§@ªí¨ç¼ÆªºVBA¼gªk
Sub sheet1_VBA()
Dim Arr, Brr, R&, S$
Arr = Range([A2], [B65536].End(3))
For R = 1 To UBound(Arr)
    S = S & "," & Arr(R, 2)
    Brr = Split(Mid(S, 2), ",")
    Arr(R, 1) = UBound(Filter(Brr, Arr(R, 2)))
Next R
Range([A2], [B65536].End(3)) = Arr
End Sub



«ÜÅãµM~~²Ä¤@ºØ¼gªk²³æ¦h¤F¡A³o­nÂk¥\©óExcelªºÁo©ú
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 6# ABK

¦r¨å+°}¦C, ¥i¸Ñ¨M«Ü¦h°ò¥»»Ý¨D, ¥iªá¨Ç®É¶¡¾Ç²ß:
Sub sheet1_VCunt()
Dim Arr, i&, xD, T$
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([Sheet1!B1], [Sheet1!B65536].End(xlUp))
For i = 2 To UBound(Arr)
    T = Arr(i, 1)
    Arr(i - 1, 1) = Val(xD(T))
    If T <> "" Then xD(T) = xD(T) + 1
Next i
[A2].Resize(UBound(Arr) - 1) = Arr
End Sub

TOP

¦^´_ 8# n7822123


    ·PÁªüÀs¤j ¼ö¤ßµ¹¤F¨â­Óµ{¦¡ !

TOP

        ÀR«ä¦Û¦b : ¬°¦Û¤v§äÂǤfªº¤H¥Ã»·¤£·|¶i¨B¡C
ªð¦^¦Cªí ¤W¤@¥DÃD