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

[µo°Ý] ¨ú¥X¥]§÷"+"or"-"¸¹¤§«áªº¼Æ­È

[µo°Ý] ¨ú¥X¥]§÷"+"or"-"¸¹¤§«áªº¼Æ­È

¤j®a¦n¡G

¥H¤U¤T­Ó«~¦W,§Ú·Q±N¶i³f¼Æ¶qªº¥[¶µor´î¶µ,´£¨ú¨ì¥]§÷Åçºâ!Åçºâ¤u§@ªíªº[F16:AJ18]
16¦T¦¬ÁY½¤(¨÷)
¤u·~¥Î¦ùÁY½¤(¨÷)
«Ê½c½¦±a(±ø)

¥]§÷³øªí!¥]§÷¤u§@ªí,¶i³f¶ñ¼g¤è¦¡¬O©T©w¦b­ì¼Æ¦rÄæ¥[¤J¶i³f¼Æ¶q,°¸º¸¤]·|¦³´î¶µªº¼Æ¶q,
´£¨ú¼Æ¶qªº·Ç«h¬°"+",«h¨ú¥¿¼Æ,"-",«h¨ú­t¼Æ,¥Ø«e¤w±N»Ý´£¨úªº¼Æ¦r,¤â°Ê¶ñ¤J¤u§@ªí¤¤,
¦ý§Ú·Q§ï¼g¬°¦Û°Ê§ì¨ú,¤£ª¾¦³¤°»ò¨ç¼Æ,¥i°w¹ï¼Æ­È"+"or"-"¸¹,¨ú¥X¥¿­t¼Æ?
¥]§÷.rar (28.83 KB)

­Y¬O»ò¨ç¼Æ,µLªk¹F¦¨,©|¤£ª¾VBA¯à§_°µ±o¨ì....¹ï¼Æ­È³Ì³Ì«á¤@­Ó"+"or"-"¸¹&¨ú¥X¥¿­t¼Æ?

TOP

¦^´_ 2# PJChen

¥¨¶°½Ð©ñ©ó"¥]§÷Åçºâ"ÀÉ®×
¸ê®Æ¦ì¸m¥H­ìªþ¥ó³]©w,¦p¦³¤£¦P½Ð¦Û¦æ½Õ¾ã

Sub ex()
Dim Arr As Variant, d As Object, a As Variant, b As Variant, X%, Y%
Set d = CreateObject("Scripting.Dictionary")
With Workbooks.Open(ThisWorkbook.Path & "\" & "¥]§÷³øªí.xlsx").Sheets("¥]§÷")
   Set Arr = .Range(.[b5], .[P7])   '¸ê®Æ½d³ò
End With
For X = 1 To Arr.Rows.Count
   For Y = 4 To Arr.Columns.Count
      If Arr(X, Y).HasFormula Then    '§PÂ_Àx¦s®æ¬O§_¬°¤½¦¡
         If Arr(X, Y).FormulaR1C1Local Like "*+*" Then     '§PÂ_¤½¦¡¤º¬O§_¬°"+"
            d(Arr(X, 1) & Arr(X, Y).Offset(-3 - X)) = Split(Arr(X, Y).FormulaR1C1Local, "+")(1)  '¸ê®Æ¼g¤J¦r¨å
         Else                                              '§PÂ_¤½¦¡¤º¬°"-"
            d(Arr(X, 1) & Arr(X, Y).Offset(-3 - X)) = "-" & Split(Arr(X, Y).FormulaR1C1Local, "-")(1)   '¸ê®Æ¼g¤J¦r¨å
         End If
      End If
   Next
Next
Workbooks("¥]§÷³øªí.xlsx").Close False
For Each a In Range([C16], [C18])      '¥]§÷°Ï¶¡
   For Each b In Range([F2], [F2].End(2))   '¤é´Á°Ï¶¡
      If d.exists(a & b) Then Cells(a.Row, b.Column) = d(a & b)  '±N¦r¨å¸ê®Æ¼g¤J¬Û²ÅªºÀx¦s®æ
   Next
Next
Set d = Nothing
End Sub

TOP

¦^´_ 3# jcchiang

±z¦n,
¸Õ°µ®É,µo¥Í°ÝÃD¦p¤U,½Ð±Ð....
1) «Ê½c½¦±a(±ø)§ì¤£¨ì¸ê®Æ
±N¸ê®Æ½d³òSet Arr = .Range(.[b5], .[P7])
§ï¬°   Set Arr = .Range(.[b5], .[AI11]),¦ýµLªk°õ¦æ

.Range(.[b5], .[AI11])ªº½d³ò,¬°¾ã­Ó¤ë¥÷ªº¸ê®Æ
¥]§÷³øªí_¨ú¥X³Ì«á¤@­Ó¥¿­t¸¹ªº­È.rar (37.61 KB)

TOP

¦^´_ 4# PJChen

1.¦]¬°¸ê®ÆÄæ¦ì¸Ì¦³ªºÄæ¦ì¨Ã¨S¦³"+","-",«o¼g¦¨"=2200"©Ò¥H³Q»~§P¬°¤½¦¡(¦p:¦h²ùªG¯È½c(¤ù),µL¥[¿}¯È½c(¤ù))
±N§PÂ_¦¡¼W¥[ "-"ªº±ø¥ó,¥u­n¨S¦³"+" & "-"´N¤£¤À©î
    If Arr(X, Y).HasFormula Then    '§PÂ_Àx¦s®æ¬O§_¬°¤½¦¡
         If Arr(X, Y).FormulaR1C1Local Like "*+*" Then     '§PÂ_¤½¦¡¤º¬O§_¬°"+"
            d(Arr(X, 1) & Arr(X, Y).Offset(-3 - X)) = Split(Arr(X, Y).FormulaR1C1Local, "+")(1)  '¸ê®Æ¼g¤J¦r¨å
         ElseIf Arr(X, Y).FormulaR1C1Local Like "*-*" Then '§PÂ_¤½¦¡¤º¬°"-"
            d(Arr(X, 1) & Arr(X, Y).Offset(-3 - X)) = "-" & Split(Arr(X, Y).FormulaR1C1Local, "-")(1)   '¸ê®Æ¼g¤J¦r¨å
         End If
      End If
2.¥]§÷³øªí¦³"«Ê½c½¦±a(±ø)"¦ý¥]§÷Åçºâ¸Ì¨S¦³"«Ê½c½¦±a(±ø)"¦Ó¬O"«Ê½c½¦±a(¨÷)"

TOP

¦^´_ 5# jcchiang

ÁÂÁÂ
²{¦b¥i¥¿±`§ì¨ú¨ì¸ê®Æ¤F

TOP

¦^´_ 5# jcchiang

±z¦n,
­ìµ{¦¡´ú¸Õ¨S°ÝÃD
¨Ó·½¸ê®Æ½d³ò¤£ÅÜ,¦ý·Q±NºI¨ú¸ü¤J¶i³fªº¸ê®ÆÂX¤j¬°
For Each a In Range([C16], [C25])
¦ý°õ¦æ«á·|¥d¦b³o¸ÌµLªkÄ~Äò,½ÐÀ°¦£¬Ý¬Ý¬O§_­n­×§ï?
d(Arr(X, 1) & Arr(X, Y).Offset(-3 - X)) = Split(Arr(X, Y).FormulaR1C1Local, "+")(1)  '¸ê®Æ¼g¤J¦r¨å
¥]§÷³øªí_¨ú¥X³Ì«á¤@­Ó¥¿­t¸¹ªº­È.rar (47 KB)

TOP

¦^´_ 7# PJChen

¿ù»~­ì¦]¬O¦]¬°
Set Arr = .Range(.[b5], .[AI11]) Åܦ¨ Set Arr = .Range(.[b2], .[AI11])
Offset¦ì¸m­×§ï§Y¥i
If Arr(X, Y).FormulaR1C1Local Like "*+*" Then     '§PÂ_¤½¦¡¤º¬O§_¬°"+"
            d(Arr(X, 1) & Arr(X, Y).Offset(-X)) = Split(Arr(X, Y).FormulaR1C1Local, "+")(1)   '¸ê®Æ¼g¤J¦r¨å
            
            ElseIf Arr(X, Y).FormulaR1C1Local Like "*-*" Then '§PÂ_¤½¦¡¤º¬°"-"
            d(Arr(X, 1) & Arr(X, Y).Offset(-X)) = "-" & Split(Arr(X, Y).FormulaR1C1Local, "-")(1)    '¸ê®Æ¼g¤J¦r¨å
         End If

TOP

¦^´_ 8# jcchiang

±z¦n,
1) §ï§¹«á,°õ¦æ¨S¦³¥ô¦ó°Ê§@
2) ½Ð°Ý ³o¬q­n«ç»ò¸ÑŪ©O?
            d(Arr(X, 1) & Arr(X, Y).Offset(-X)) = Split(Arr(X, Y).FormulaR1C1Local, "+")(1)   '¸ê®Æ¼g¤J¦r¨å            
            ElseIf Arr(X, Y).FormulaR1C1Local Like "*-*" Then '§PÂ_¤½¦¡¤º¬°"-"
            d(Arr(X, 1) & Arr(X, Y).Offset(-X)) = "-" & Split(Arr(X, Y).FormulaR1C1Local, "-")(1)    '¸ê®Æ¼g¤J¦r¨å
¥]§÷³øªí_¨ú¥X³Ì«á¤@­Ó¥¿­t¸¹ªº­È.rar (46.57 KB)

TOP

¦^´_ 8# jcchiang

¤£ª¾¹Dµo¥Í¤°»ò¨Æ,­ì¨Óµ{¦¡¤]¤£¯à¹B§@¤F....
   ¥]§÷³øªí_¨ú¥X³Ì«á¤@­Ó¥¿­t¸¹ªº­È.rar (39.36 KB)

TOP

        ÀR«ä¦Û¦b : «Î¼e¤£¦p¤ß¼e¡C
ªð¦^¦Cªí ¤W¤@¥DÃD