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

½Ð°Ý¦p¦ó±N¦h¤u§@ªíªºªí®æ¸ê®Æ¶×Á`¦b¤@±i¤u§@ªí

½Ð°Ý¦p¦ó±N¦h¤u§@ªíªºªí®æ¸ê®Æ¶×Á`¦b¤@±i¤u§@ªí

¥»©«³Ì«á¥Ñ gaishutsusuru ©ó 2019-6-1 12:40 ½s¿è

¹Ï1



¹Ï2



½Ð°Ý¦p¦ó±N©Ò¦³¤u§@ªí¤º¹Ï1ªº¸ê®Æ¡A¶×Á`¦b¦P¤@±i¤u§@ªí(¹Ï2)?
1. ¹Ï1ªº¸ê®Æ·|¦³31¤é¡A¦ý¦]¬°Â²¤Æ¡A©Ò¥H¥u©ñ6¤é
2. ¦b¹Ï2ªº½u§O¡A·|¨Ì·Ó¤u§@ªí¦WºÙ(¨Ò¡G1-1¡A1-2¡K)¨Ó¶ñ¼g
3. ¤u§@ªí¦WºÙ¥Nªí½u§O¡G1-1¡A1-2¡A¡K¡C¦]¬°Â²¤Æ¡A©Ò¥H¥Ø«e¥u©ñ2­Ó¡C¦ý§Æ±æ¯àŪ¨ú©Ò¦³ªº¡C

¬¡­¶Ã¯1.zip (9.47 KB)
·Q½Ð°Ý¤j®a¦p¦ó¹F¦¨¹Ï2ªºµ²ªG©O? ¤@¯ëªº¤½¦¡¦³¿ìªk¶Ü? ÁÙ¬O¦³VBAªº¤è¦¡©O?

«ô°U¤j®a¨ó§U¡A·PÁ¡C

Sub TEST()
Dim Sht As Worksheet, UR As Range, xR As Range, i%, c&, r&, N&, ARR
ReDim ARR(1 To 30000, 1 To 6)
Sheets("¤u§@ªí2").UsedRange.Offset(1, 0).EntireRow.Delete
For Each Sht In Sheets
    If Sht.Name Like "#-#" = False Then GoTo 101
    Set UR = Sht.UsedRange
    For r = 1 To UR.Rows.Count Step 5
    For c = 1 To UR.Columns.Count Step 3
        Set xR = UR(r + 1, c)
        If Application.Sum(xR(2, 3).Resize(3)) = 0 Then GoTo 99
        N = N + 1
        ARR(N, 1) = "'" & Sht.Name: ARR(N, 2) = xR(2): ARR(N, 3) = xR(0)
        For i = 1 To 3
            ARR(N, 3 + i) = xR(i + 1, 3)
        Next
99: Next c
    Next r
101: Next
[¤u§@ªí2!A2:F2].Resize(N) = ARR
End Sub

Xl0000341.rar (13.95 KB)

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

TOP

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

«D±`·PÁ±zªº¨ó§U¡C

¦ý¦³¥t¤@¥ó¨Æ·Q½Ð±Ð±z¡C¤£ª¾¹D±z¤è¤£¤è«Kµy·L»¡©ú¤@¤U±z¼gªºVBA¬O«ç»ò¹B§@ªº¡C

¦]¬°¦pªG¤§«áªí®æ¦³§ï°Ê¡A¦Û¤v·Q¸ÕµÛ¾Þ§@¬Ý¬Ý¡C

¤£¦n·N«ä¡A¦pªG±z¤è«Kªº¸Ü¡A¦A³Â·Ð±z¤F¡AÁÂÁ¡C

TOP

¦^´_ 3# gaishutsusuru

1) ¨C3Äæ+¨C5¦æ ¬°¤@­Ó°Ï¶ô
2) "¼ÐÃD¦æ"¦b¨C¤@°Ï¶ôªº²Ä2¦æ, ¥H xR ©w¦ì[«¬¸¹]³o¤@Àx¦s®æ
3) xR¥k²Ä3Äæ¤Uªº3­ÓÀx¦s®æ, ¬°¤T­Ó¤Ø¤oªº[¼Æ¶q], ­Y¼Æ¶q¥[Á`¬°0, ªí¥ÜµL°O¿ý, ²¤¹L
4) ¨úxR¤W¤@®æ¬°[?¤é], xR¤U¤@®æ¬°[«¬¸¹¤º®e], ¶ñ¤JArr¬ÛÀ³¦ì¸m
5) ³Ì«á¥HN¬°¦æ¼Æ, ¶ñ¤JÁ`ªí

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

TOP

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


±z¦n¡AÁÂÁ±zªº¸Ô²Ó¸Ñ»¡¡A«D±`®e©ö²z¸Ñ¡C

ÁÂÁ±z¡C

TOP

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


  ­ã´£³¡ªL¤j¤j±z¦n¡A

¤£¦n·N«ä¡A¦A¦¸¥´ÂZ¤F¡C

¦bµ{¦¡½X¤¤¡A¡uIf Sht.Name Like "#-#" = False Then GoTo 101¡v¡Aªí¥Ü©Ò¦³ªº#-#¦WºÙªº¤u§@ªí³£·|³QŪ¨ú¡C

¦ý§Ú­è­èµo²{¤u§@ªí¤¤¦³ªº¬O#-#-#ªº¦WºÙ¡C©Ò¥H¦pªG­n¦P®É³£¥i¥HŪ¨ú"#-#" ©M"#-#-#"¦WºÙªº¤u§@ªí¡A½Ð°Ý­n¦p¦ó§ó§ï©O?

§Ú¤Wºô¬d¸ê®Æ¡G If Sht.Name Like "#-#" And Sht.Name Like "#-#-#"= False Then GoTo 101¡C¦ý«o¤£¯à¦P®ÉŪ¨ú³o2ºØ¦WºÙªº¤u§@ªí¡C


¦A«ô°U±z¨ó§U´£¨Ñ¤@¨Ç·Qªk¡A·PÁ±z¡C

TOP

¦^´_ 6# gaishutsusuru


If (Sht.Name Like "#-#") + (Sht.Name Like "#-#-#") = 0 Then GoTo 101

"#-#"  ªí¥Ü1¦ì¼Æªº¦ê±µ, ¦p:5-6,  ­Y¬°5-16§Y¬°¤£²Å¦X,
¥ç§Y¤@­Ó"#"¬°¤@­Ó¼Æ¦r. "####" ´N¬O«ü"0000"~"9999"ªº½d³ò,

"*#-#*" , ¥u­n¤¤¶¡¬O1¦ì¼Æ¦ê±µªº³£²Å¦X, "*"¥i¥H¬O¥ô·Nªø«×ªº¦r¤¸(¼Æ¦r©Î¤¤­^¤å©Î²Å¸¹)
"??-??" , «h¬O"-"ªº«e«á­­©w¥u¯à¦³2­Óªø«×ªº¥ô·N¦r¤¸

TOP

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


ÁÂÁ±z¦A¦¸¸Ô²Óªº¸Ñ»¡¡AÁÂÁÂ

TOP

        ÀR«ä¦Û¦b : ¯à¥I¥X·R¤ß´N¬OºÖ¡A¯à®ø°£·Ð´o´N¬O¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD