☰

仅当 Excel 中另一列显示“更新”时才更新值

tag-icon • tag-icon microsoft-excel worksheet-function vba
仅当 Excel 中另一列显示“更新”时才更新值

我创建了一个名为 =SalePrice 的函数，它将根据产品的百分比标记“成本价”列中的值。

现在我希望能够检查名为 UpdatePrice 的相邻列以查看产品的销售价格是否必须保持不变。

所以我想创建一个可以执行以下功能的函数：

IF(UpdatePrice = "Update Price",SalePrice,KeepOldValue)

示例表

因此，只有当 V 列 =“更新价格”时，我才想更新 F 列中的值，否则 F 列中的值应保持不变。

你会怎样做呢？

答案1

看起来您正在使用表格，这是一个很好的开始。添加一些列名，例如 UpdatePrice 而不是 CustomField10。然后在公式中使用这些列名，例如=IF([@UpdatePrice]="Update Price", [@SalePrice], [@FullPrice])。

答案2

因此，我所做的是：

创建一个宏

'Select UpdatePrices sheet.
    Sheets("UpdatePrices").Select
'Select All the rows containing values and delete them.
    Application.Goto Reference:="R2C1"
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.EntireRow.Delete
'Select the ERP sheet with the latest values and copy paste them to the UpdatePrices Sheet
    Sheets("ManagerImportSheet").Select
    Application.Goto Reference:="R2C1"
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Sheets("UpdatePrices").Select
    Application.Goto Reference:="R2C1"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'Select the Old Sale Prices and copy them.
    Application.Goto Reference:="R2C1"
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 5).Range("UpdatePrices[[#Headers],[Code]]").Select
    Range("UpdatePrices[SalePrice]").Select
    'Range("F1476").Activate
    Application.CutCopyMode = False
    Selection.Copy
'Go to Data Sheet and Paste them for later use in formula
    Sheets("Data").Select
    Application.Goto Reference:="R2C3"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'Clear contents of Sale Price and Cost Price columns
    Sheets("UpdatePrices").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Application.Goto Reference:="R2C1"
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 19).Range("UpdatePrices[[#Headers],[Code]]").Select
    Range("UpdatePrices[CustomFields.8220bcd8c4284ee9b653cced0445a0cd]").Select
    Selection.ClearContents
'Enter the named formula to calculate the new CostPrices.
    Application.Goto Reference:="R2C20"
    ActiveCell.FormulaR1C1 = "=CostPrice"
    Application.Goto Reference:="R2C6"
'Enter a formula that will check the "Update Price" Column named '[@[CustomFields.49d90e39488947ef899044e3c56a23f5] and if it is equal to
' "Update Price" then use the named formula SalePrice else, copy the old price into
' Place from the Data sheet.
    ActiveCell.FormulaR1C1 = _
        "=IF([@[CustomFields.49d90e39488947ef899044e3c56a23f5]]=""Update Price"",SalePrice,Data!RC[-3])"

剩下要做的可能就是锁定相应的工作表/单元格。

相关内容