VBA 处理数据

最新推荐文章于 2024-08-13 08:39:15 发布

苏坡爱豆的笑容都没你的甜

最新推荐文章于 2024-08-13 08:39:15 发布

阅读量373

点赞数

文章标签：其他

本文链接：https://blog.csdn.net/weixin_44656422/article/details/124503234

版权

这个博客主要介绍了如何使用VBA遍历Excel工作表的所有列，并填充公式。内容涉及 WorksheetFunction 的使用，如何将公式转换为值，以及在VBA中处理Linux时间表达。此外，还展示了如何通过多重条件判断来计算特定需求，例如根据条件计算平均值并进行四舍五入。最后，博客提供了一个用于保存CSV文件的自动化过程，并显示了整个操作的运行时间。

摘要由CSDN通过智能技术生成

要点

如何遍历所有列

colLong = Sheet1.UsedRange.Rows.Count
oriLong = 2

For i = oriLong To colLong

D = "D" & i

如何将一整列用公式填充

很多excel的公式加上WorksheetFunction.前缀，就可以使用

如何公式变值

For Each cell In Selection

cell.Value = cell.Value

VB中的Linux时间表达

DateDiff("s", "01/01/1970 00:00:00", Now())

多个if叠加

IF XXX THEN

ELSE

END IF

在套if的时候，里面的END IF也要写在里面

Sub buttom_Click()
Application.ScreenUpdating = False
    Dim t
    Dim rng As Range
    Dim i As Integer
    Dim D As String
    Dim W As String
    Dim N As String
    Dim H As String
    Dim F As String
    Dim colI As String
    Dim G As String
    Dim L As String
    Dim cell As Range
    t = Timer
    colLong = Sheet1.UsedRange.Rows.Count
    oriLong = 2
    
    
    Range("N2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    
    For i = oriLong To colLong
    
    D = "D" & i
    W = "W" & i
    N = "N" & i
    H = "H" & i
    F = "F" & i
    colI = "I" & i
    G = "G" & i
    L = "L" & i
'    Range(W).Value = WorksheetFunction.CountIf(Range("D1:" & D), Range(D))
'   If Range(W).Value = 1 Then
    Min = Range(F).Value
    Rank = WorksheetFunction.CountIf(Range("D1:" & D), Range(D))
    
    
    If Min > 0 Then
        If Rank = 1 Then
            Demand = WorksheetFunction.Sum(Range(colI & ":I" & (i + Min - 1)))
            week_Avg_request = Demand / Min
            If Demand = 0 Then
                Range(N).Value = Range(G).Value
            Else
                Range(N).Value = WorksheetFunction.RoundDown(Range(L).Value / week_Avg_request, 0)
            End If
        Else
            Demand = ""
            week_Avg_request = ""
        End If
    ElseIf Min <= 0 Then
        If Rank = 1 Then
            Demand = 0
            week_Avg_request = 0
            Range(N).Value = Range(G).Value
        Else
            Demand = ""
            week_Avg_request = ""
        End If
    End If

'    If Rank = 1 Then
 '       If Demand = 0 Then
  '          Range(N).Value = Range(G).Value
   '     Else
    '        Range(N).Value = WorksheetFunction.RoundDown(Range(L).Value / week_Avg_request, 0)
     '   End If
   ' End If


    Next
    
    Columns("N:N").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=IF(R[-1]C-1<0,0,R[-1]C-1)"
    
'    For Each cell In Selection
'    cell.Value = cell.Value
'    Next



ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\InventoryForecast_ImportData" & DateDiff("s", "01/01/1970 00:00:00", Now()) & ".csv", FileFormat:=xlCSVUTF8
Application.ScreenUpdating = True
MsgBox "CSV已生成，" & "总运行时间为" & Timer - t & "秒"
End Sub