求助：如何可以做到根据计划显示生产进度？

xqgg2007 · 发表于 2022-11-14 16:04

刚把公式和条件格式学明白，下一步学Power系列，之后才是VBA。

Meteor-渣渣 · 发表于 2022-11-14 16:06

xqgg2007 发表于 2022-11-14 15:57
VBA我还没学呢。我不是为了完成这个工作，我是要整明白怎么弄。

所以说了这么多，你还不知道怎么搞？
你向批量就vba，不会vba就手动

xingqiliu666 · 发表于 2022-11-14 16:22

Sub 批量加数据条()
Application.ScreenUpdating = False
Dim i As Integer, j As Integer, row As Integer, temp As String
With Sheet1
row = .Range("a65536").End(xlUp).row
.Range("R3").Activate
Application.CutCopyMode = False
.Range("R3:AA" & row).FormatConditions.Delete
For i = 3 To row
For j = 18 To 27
If (.Cells(i, j - 10).Value2 <> "") Then
temp = .Cells(i, j - 10).Value2
Else
temp = 0
End If
.Cells(i, j).Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=temp
End With
With Selection.FormatConditions(1).BarColor
.Color = 13012579
.TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillSolid
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
Next j
Next i
End With
Application.ScreenUpdating = True
MsgBox "添加完成！！！"
End Sub

复制代码

xingqiliu666 · 发表于 2022-11-14 16:36

Sub 批量加数据条()
Application.ScreenUpdating = False
Dim i As Integer, j As Integer, row As Integer, temp As String
With Sheet1
      row = .Range("a65536").End(xlUp).row
      .Range("R3").Activate
      Application.CutCopyMode = False
      .Range("R3:AA" & row).FormatConditions.Delete
      For i = 3 To row
         For j = 18 To 27
            temp = "=" & .Cells(i, j - 10).Address()
            .Cells(i, j).Select
               Selection.FormatConditions.AddDatabar
            Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
            Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
            With Selection.FormatConditions(1)
                  .MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
                  .MaxPoint.Modify newtype:=xlConditionValueFormula, newvalue:= _
                  temp
            End With
            With Selection.FormatConditions(1).BarColor
                  .Color = 13012579
                  .TintAndShade = 0
            End With
            Selection.FormatConditions(1).BarFillType = xlDataBarFillSolid
            Selection.FormatConditions(1).Direction = xlContext
            Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
            Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
            Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
            With Selection.FormatConditions(1).AxisColor
                  .Color = 0
                  .TintAndShade = 0
            End With
            With Selection.FormatConditions(1).NegativeBarFormat.Color
                  .Color = 255
                  .TintAndShade = 0
            End With
         Next j
      Next i
End With
Application.ScreenUpdating = True
MsgBox "添加完成！！！"
End Sub
vba这个是最新的，将数据条的最大值改为了公式，然后批量添加

xingqiliu666 · 发表于 2022-11-14 16:41

7.23出货明细.zip (176.24 KB, 下载次数: 4)
这个是附件

lj_healthy · 发表于 2022-11-14 16:48

本帖最后由 lj_healthy 于 2022-11-14 17:03 编辑

我也想知道如何处理，在线等

xqgg2007 · 发表于 2022-11-15 14:30

xingqiliu666 发表于 2022-11-14 16:41
这个是附件

多谢，多谢。没能打开，电脑把宏命令给阻止了。

xqgg2007 · 发表于 2022-11-15 14:35

我自己用比较笨的办法实现了。因为这个生产量还是有一定规律的，所以我在表的上面做了响应的格式设置，然后用格式刷对下面的数据相同的区域用格式刷批量设置。用格式刷设置时，我让生产区域的数值等于计划的1/2，这样在刷的过程中，是否选错的格式也一目了然。

xqgg2007 · 发表于 2022-11-15 14:36

lj_healthy 发表于 2022-11-14 16:48
我也想知道如何处理，在线等

回复见上面..................

		自动登录	找回密码
密码			免费注册

[求助] 求助：如何可以做到根据计划显示生产进度？