时间戳

首页 > 解决方案 > 将范围乘以当月剩余天数的百分比

vba - 将范围乘以当月剩余天数的百分比

问题描述

我在编写这段代码的最后一部分时遇到了麻烦。

我试图让 excel 根据今天计算当月剩余天数的百分比。然后它将一系列单元格乘以该值。

当它实际相乘时，所有单元格都变为“#NAME”。我检查了 msgbox，它的计算部分运行正确（它找到了正确的百分比），但是当插入最后一个公式进行乘法时，它不起作用。

当我从公式中取出变量并输入实际数字时，它工作正常。我认为我将变量写入最终序列的方式有问题。我的格式有什么问题？

Sub MultiplyDayRatio()

Dim rngData As Range
Dim MyDate As Date
Dim DaysLeft As Integer
Dim DaysInMonth As Integer
Dim PercentLeft As Double


MyDate = Date

'DaysInMonth = EoMonth(Today(), 0)

DaysLeft = WorksheetFunction.EoMonth(Date, 0) - Date

'MsgBox DaysLeft

DaysInMonth = (MonthDays(Month(MyDate)))

'MsgBox DaysInMonth

PercentLeft = DaysLeft / DaysInMonth

'MsgBox PercentLeft

Set rngData = ThisWorkbook.Worksheets("Ingredient_Forecast_Summary").Range("G3:G70")
rngData = Evaluate(rngData.Address & "*PercentLeft.value")


End Sub

标签： vbaexcel

解决方案

洛佩里。试试这个：

Sub MultiplyDayRatio()

    Dim rngData As Range, C As Range '<-- new variable
    Dim MyDate As Date
    Dim DaysLeft As Integer
    Dim DaysInMonth As Integer
    Dim PercentLeft As Double


    MyDate = Date

    'DaysInMonth = EoMonth(Today(), 0)

    DaysLeft = WorksheetFunction.EoMonth(Date, 0) - Date

    'MsgBox DaysLeft

    DaysInMonth = (MonthDays(Month(MyDate)))

    'MsgBox DaysInMonth

    PercentLeft = DaysLeft / DaysInMonth

    'MsgBox PercentLeft

    Set rngData = ThisWorkbook.Worksheets("Ingredient_Forecast_Summary").Range("G3:G70")
    'rngData = Evaluate(rngData.Address & "*PercentLeft.value") 'instead this

    'Try this
    For Each C In rngData
        C = C * PercentLeft
    Next C

End Sub

推荐阅读