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

[µo°Ý] excel ¦Û°Ê¦X¨ÖÀx¦s®æ

¦^´_ 8# Andy2483

·PÁÂAndy2483«e½ú¤j¤jªº¸Ñ»¡
Åý¹ïVBA³o¶ôÁÙ¬O¤p¥Õªº§Ú¥i¥H§ó¦nªº²z¸Ñ
«D±`·PÁ¡I

TOP

¦^´_  samwang

S¤j
¥H¤U¬°¦³°ÝÃDªºÀÉ®×
¥t¥~
¤é´Áªº²£¥Í¬O
°£¤F°_©l¤é´Á(²Ä¤@¤Ñ)¬O¤â°Ê¶ñ¤J ...
¬PªÅÉ@¦ÐÁl µoªí©ó 2022-11-29 14:55


·s¼W¦p¤U¬õ¦r³¡¤À¡A½Ð¦A´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

Sub test()
Dim Arr, xD, C%, T%, T1%
Application.DisplayAlerts = False
Set xD = CreateObject("Scripting.Dictionary")
C = Cells(5, Columns.Count).End(xlToLeft).Column
Range([e4], Cells(4, C)).UnMerge '¸Ñ°£²Ä¥|¦C¦X¨ÖÀx¦s®æ
Range([e4], Cells(4, C)).Clear   '²M°£¸ê®Æ

Arr = Range([e5], Cells(5, C))
For j = 1 To UBound(Arr, 2)
    T = Month(Arr(1, j))
...
...
...

TOP

¦^´_ 8# Andy2483

³o­ÓV= split¥Îªº¯u¦n¡A¥t¥~¡A½Ð±Ð¤@¤UxD(x)(1)¡A¬°¤°»ò(1)? ³o¬O¤°»ò·N«ä? ÁÂÁÂ

V = Split(",¤@,¤G,¤T,¥|,¤­,¤»,¤C,¤K,¤E,¤Q,¤Q¤@,¤Q¤G", ",")
'¡ô¥OV¬O¦r¦ê¥Î ","²Å¸¹¤À³Îªº¤@ºû°}¦C
For Each x In xD.Keys
   xD(x).UnMerge
   xD(x).Merge
   xD(x).HorizontalAlignment = xlCenter
   mm = Split(x, "/")(1)
    xD(x)(1) = V(mm) & "¤ë"
   '¡ôxD¦r¨å¸Ì°j°éKey¹ïÀ³ªº Àx¦s®æ¶°²Ä¤@®æ¶ñ¤J¤å¦r:
    '¤å¦r¬O¥Îmm¼Æ¦r§ì¨ú V¤@ºû°}¦C¸Ì¹ïÀ³ªº°ê¦r¼Æ¦r¦ê,«á­±¦A¥[ "¤ë"¦r
Next

TOP

¦^´_ 13# samwang


    ÁÂÁ«e½ú¦^´_
5:00«á®a¸Ì¦£!©ú¤Ñ¦AÄ~Äò°Q½×!
ÁÂÁÂ

TOP

¦^´_ 13# samwang


    «e½ú¦­¦w
1.V = Split(",¤@,¤G,¤T,¥|,¤­,¤»,¤C,¤K,¤E,¤Q,¤Q¤@,¤Q¤G", ",")¬O§Û¨Óªº!¾Ç ­ã´£³¡ªL«e½úªº½d¨Òµy°µÅܳq
http://forum.twbts.com/viewthrea ... eD2&ordertype=1
For j = 1 To 9 'i = 2 ³]°j°é±N¸ê®Æ±a¤JCrr°}¦C²Ä¤@¦C
        Crr(xD(T1), j) = Arr(i, Array(9, 10, 11, 12, 22, 23, 24, 8, 5)(j - 1))
        '¡ô·íi = 2:¤@¶}©l Crr(xD(T1), j) = Crr(1, j) ¦]¬° xD(T1)=1
        '  ,Array()«ü©w©ñ¤JªºÄæ¦ì,(j - 1)¬O¦]¬°Arrayªº²Ä¤@µ§¯Á¤Þ¬O0
        '¡ô·íi >= 3:±M¯S®×¸¹³£¬OA2009001,©Ò¥H¦b«e¤èxD(T1)³£¦³¥[1  xD(T1) = xD(T1) + 1
        '¡ôª½¨ìi = 50:±M¯S®×¸¹Åܦ¨A2104001,xD(T1)Åܦ¨1
        '¡ô¨ìi = 59:±M¯S®×¸¹¤SÅܦ¨A2009001,©Ò¥HxD("A2009001") ITEM¦b«e¤è¤wÄ~Äò¥[1
Next j

2.xD(x)(1)¡A¬°¤°»ò(1)? ³o¬O¤°»ò·N«ä?  «á¾Ç¤]¤£ª¾¹D!¶Ã²qªº!é¨ìªº
¦]¬°¾Ç²ß°}¦C»P¦r¨åªº¼ö±¡¨ÏµM,¯à¥Î°}¦C©M¦r¨åªº¾÷·|³£¤£·Q©ñ¹L,
°}¦C©M¦r¨å¹J¨ì°ÝÃDªº¤è¦¡´N¬O:
1.§â¾Ç²ß¹Lµù¸Ñ¹Lªº©«¤l®³¨Ó¹B¥Î
2.¼Æ¦r¤Æªº¶Ã¸Õ!¸Õ¦¨¥\¤F´N§ó¦³°Ê¤O
3.­Y¤£¦¨¥\!¬dºô¸ô¬ÛÃöª¾ÃÑ,Ä~Äò¶Ã¸Õ
4.¦A¤£¦¨¥\!´N¦b½×¾Â¶}·s¥DÃDµo°Ý
5.¥­±`´N§â«e½ú­Ìªº¤ß¦å³Ð§@©«¤@¦r¤@¦æ¤@°}¦C¤@¦r¨å¤@°j°é¤@©«ªºµù¸Ñ
5.1.½T©w¦Û¤vªº«ä¦Ò.ÅÞ¿è¬O§_¥¿½T!©ñ¤W½×¾Â½Ð«e½ú­Ì¦A«ü¾É,¥H«á½Æ²ß¤]¤è«K
5.2.¥H«e¨«°¨¬Ýªá!»{¬°À´ªº³£¬OéÀ´!»{¬°¤£À´ªº´N±a¹L¬O«á¾Ç¤£¿n·¥ªº¾Çªk
5.3.²{¦b±`¤£¬ÝÃD·N!¤£·Qª¾¹Dºô¤Í°Ý¤°»ò!ª½±µ¶}©lµù¸Ñ ­ã´£³¡ªL«e½úµ{¦¡½X!³Ì«á¤~ª¾¹DÃD·N
5.4.¤å¦r±Ô­zªºÅÞ¿è.¤f±ø¤]·QÄ~Äò¾Ç²ß
6.¤T¤H¦æ¥²¦³§Ú®v!³Ì­«­nªº¬O¦³¦ñ!ÁÂÁ«e½ú
7.ÁÂÁ½׾Â!ÁÂÁ«e½ú­Ìªº«ü¾É!ÁÂÁÂ

TOP

¦^´_ 13# samwang


    ¸g´ú¸Õµ²ªG:
'xD(x)(1) = xD(x).Offset(0, 0)(1, 1)
'xD(x)(1) = xD(x).Offset(0, 0)(1)
'xD(x)(1) = xD(x).Offset(0, 0)

'xD(x)(1) = xD(x).Item(1, 1)(1, 1)
'xD(x)(1) = xD(x).Item(1, 1)(1)
'xD(x)(1) = xD(x).Item(1, 1)

'xD(x)(1) = xD(x).Cells(1, 1)(1, 1)(1, 1)
'xD(x)(1) = xD(x).Cells(1, 1)(1, 1)
'xD(x)(1) = xD(x).Cells(1, 1)(1)
'xD(x)(1) = xD(x).Cells(1, 1)

'xD(x)(1) = xD(x)(1, 1)
©Ò¥HÀ³¸Ó¬OÀx¦s®æ¶°¸Ìªº¦ì¸m!
¥i¬O³o¼Ë»¡¤]ÁÙ¤£·Ç,³o©«ªº±¡¹Ò¬O­n¦X¨ÖÀx¦s®æ,©Ò¥HÀx¦s®æ¶°¤@©w¬O³sÄòªº¬Û³sÀx¦s®æ,
¤£ª¾¹D¸õ®æªºÀx¦s®æ¶°¥i¤£¥i¥H³o¼Ë¥Î?? «Ý«á¾Ç«áÄò¬ã¨s¨ìµ²ªG¦A¨Ó¦¹¥DÃD¦^´_

TOP

¦^´_ 15# Andy2483

·PÁ±zªº¸Ñ»¡¡A¥t¥~³o­ÓSplitÅý«á¾Ç·Q¨ì¤]¥i¥H§ï¦¨Array¡AÁÂÁÂ
V = Split(",¤@,¤G,¤T,¥|,¤­,¤»,¤C,¤K,¤E,¤Q,¤Q¤@,¤Q¤G", ",")
V = Array("", "¤@", "¤G", "¤T", "¥|", "¤­", "¤»", "¤C", "¤K", "¤E", "¤Q", "¤Q¤@", "¤Q¤G")

TOP

¦^´_ 13# samwang


    ÁÂÁ«e½ú¤@°_¬ã°Q xD(x)(1)
¸g«á¾Ç¬ã¨sµ²ªG¸ò16¼Ó®t²§«Ü¤j! ½Ð«e½ú½Æ»s¤U¦Cµ{¦¡½X´ú¸Õ´N·|ª¾¹D¤F!
«á¾Ç¤£ª¾¹D¸Ó«ç»ò±Ô­z¥LªºÅÞ¿è

Option Explicit
Sub Union_test_1()
Dim xU As Range
Set xU = [A1:A2]
Set xU = Union(xU, [C1].Resize(2, 1))
Set xU = Union(xU, [E5].Resize(2, 1))
MsgBox xU(15).Address
End Sub
Sub Union_test_2()
Dim xU As Range
Set xU = [A1:A2]
Set xU = Union([C1].Resize(2, 1), xU)
Set xU = Union([E5].Resize(2, 1), xU)
MsgBox xU(15).Address
End Sub
Sub Union_test_3()
Dim xU As Range
Set xU = [A1:A2]
Set xU = Union(xU, [B1].Resize(2, 1))
Set xU = Union(xU, [E5].Resize(2, 1))
MsgBox xU(15).Address
End Sub
Sub Union_test_4()
Dim xU As Range
Set xU = [A1:A2]
Set xU = Union([B1].Resize(2, 1), xU)
Set xU = Union([E5].Resize(2, 1), xU)
MsgBox xU(15).Address
End Sub
Sub Union_test_5()
Dim xU As Range
Set xU = [A1:A2]
Set xU = Union([F5].Resize(2, 1), xU)
Set xU = Union([E5].Resize(2, 1), xU)
MsgBox xU(5).Address
End Sub
Sub Union_test_6()
Dim xU As Range
Set xU = [A1:A2]
Set xU = Union(xU, [C1].Resize(2, 1))
Set xU = Union(xU, [E5].Resize(2, 1))
MsgBox xU(15, 1).Address
End Sub
Sub Union_test_7()
Dim xU As Range
Set xU = [A1:A2]
Set xU = Union(xU, [C1].Resize(2, 1))
Set xU = Union(xU, [E5].Resize(2, 1))
MsgBox xU(1, 15).Address
End Sub
Sub Union_test_8()
Dim xU As Range
Set xU = [A1:A2]
Set xU = Union(xU, [C1].Resize(2, 1))
Set xU = Union(xU, [E5].Resize(2, 1))
MsgBox xU(15, 15).Address
End Sub

TOP

¦^´_ 18# Andy2483


¸g±zªº»¡©ú¤w¤F¸Ñ¡A·PÁÂ

TOP

¦^´_ 12# samwang

·PÁÂS¤j
¤w´ú¸Õ§¹¦¨
¥\¯à¥¿±`¡I
«D±`·PÁ¡I

¥t¥~·Q¦A°l£¸¶µ¥\¯à
½Ð°Ý¤ë¥÷¦X¨Ö«á¯à¦Û°Ê¥[¤J¥~®Øªº®æ½u¶Ü?

¦A³Â·Ð¤F¡A·PÁ¡I

TOP

        ÀR«ä¦Û¦b : ¥Í®ð¡A´N¬O®³§O¤Hªº¹L¿ù¨ÓÃg»@¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD