Airman
Lv 4
Airman asked in 電腦與網際網路軟體 · 1 decade ago

VBA~請修正公式語法錯誤之處。謝謝!!!

範例附檔︰http://www.FunP.Net/652618

T7=IF(OR(T$6="",$S7="",$S7>=T$6,T$3<=0,$S7+T$3-X<=0,$S7-X<=0),"",IF(OR($S$5=OFFSET($I$6,$S7+T$3,1,,7))*OR(SUM((OFFSET($I$6,$S7,1,,7)=$S$5)*OFFSET($A$7,$S7-X,1,,7))=OFFSET($A$7,$S7+T$3-X,1,,7)),$S7+T$3,""))

以效果檔的資料表格式而言︰

第二段公式~

1. =$S7的 I 欄期數之J︰P=$S$5數值的儲存格標示淺藍色。

2.上述=$S$5的儲存格(含)向左=$S7-X之 A 欄期數的B︰H之對應數值儲存格標示淺藍色。

3.上述之對應數值亦有顯示在=$S7+T$3-X的 A 欄期數之B︰H的儲存格,亦標示淺藍色。

詳如︰需求範例。 (備註︰X=T$6-$S7的差值。)

本題需求︰

表達第二段公式的語法(第145~148列)有誤,應該如何修正?

懇請 各位大師與先進惠予指教!!! 謝謝!!!

1 Answer

Rating
  • 顯栓
    Lv 7
    1 decade ago
    Favorite Answer

    Dim Rx, Ry

    列127

    Ry = Sheets(1).Range("S" & b.Row) * 2 - Sheets(1).[T6] + 7 '$S7-X

    Sheets(1).Range("J" & Ry & ":" & "P" & Ry).Select

    Selection.FormatConditions.Delete '設定格式化條件清除

    '公式為:

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=J" & Ry & "=$S$5"

    Selection.FormatConditions(1).Interior.ColorIndex = 8 '設定格式化條件 (圖樣顏色)

    Sheets(1).Range("B" & Ry & ":" & "H" & Ry).Select

    Selection.FormatConditions.Delete

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=J" & Ry & "=$S$5"

    Selection.FormatConditions(1).Interior.ColorIndex = 8

    Rx = Sheets(1).Range("S" & b.Row) * 2 + Sheets(1).[T3] - Sheets(1).[T6] + 7 '$S7+T$3-X

    Sheets(1).Range("J" & Rx & ":" & "P" & Rx).Select

    Selection.FormatConditions.Delete

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=J" & Rx & "=$S$5"

    Selection.FormatConditions(1).Interior.ColorIndex = 27

    Sheets(1).Range("B" & Rx & ":" & "H" & Rx).Select

    Selection.FormatConditions.Delete

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

    "=COUNTIF(B" & Rx & ",SUMIF($J" & Ry & ":$P" & Ry & ",$S$5,$B" & Ry & ":$H" & Ry & "))"

    Selection.FormatConditions(1).Interior.ColorIndex = 8

    請修改測試!

Still have questions? Get your answers by asking now.