时间戳

首页 > 解决方案 > 按钮乘法范围数据和按钮删除此乘法

excel - 按钮乘法范围数据和按钮删除此乘法

问题描述

我有两个按钮，“乘以 0”和“显示原始值”。

对于“乘以 0”按钮，我有下面的代码，它工作正常。我需要帮助的是第二个按钮的代码，它将使乘以 0 的范围返回到其原始数字）。

Public Sub MultiplyByZero()

Dim rngData As Range

Set rngData = ThisWorkbook.Worksheets("Input Sheet LC").Range("I76:O103")
rngData = Evaluate(rngData.Address & "*0")

End Sub

谢谢你的帮助！

标签： excelvba

解决方案

使用公式而不是常数：

Public Sub MultiplyByZero()
    Dim rngData As Range, rngWork AS Range

    Set rngData = ThisWorkbook.Worksheets("Input Sheet LC").Range("I76:O103")
    For Each rngWork In rngData.Cells
        With rngWork
            If .HasFormula Then
                If Right(.Formula,2) <> "*0" Then .Formula = .Formula & "*0"
            Else
                .Formula = "=" & .Value & "*0"
            End If
        End With
    Next rngWork
End Sub

Public Sub DivideByZero()
    Dim rngData As Range, rngWork AS Range

    Set rngData = ThisWorkbook.Worksheets("Input Sheet LC").Range("I76:O103")
    For Each rngWork In rngData.Cells
        With rngWork
            If .HasFormula Then
                If Right(.Formula,2) = "*0" Then .Formula = Mid(.Formula, 1, Len(.Formula)-2)
            End If
        End With
    Next rngWork
End Sub

这将10变为=10*0然后再变为=10

推荐阅读