使用VBA在范围内的依赖单元格上添加公式 | 使用VBA在范围内的依赖单元格上添加公式

本文介绍了使用VBA在范围内的依赖单元格上添加公式的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想使用VBA在单元格上插入一个计算.这是我现在将其插入的方式.它的工作相当不错，但是我不能修改发票表上的百分比.我希望在插入行后可以修改百分比，它会自动更新售价.

I want to insert a calculation on a cell using VBA. Here is how i insert it right now. it's work pretty Good but i cannot mod the percent on the invoice sheet. I want that after i insert the row i can modify the percent and it will update automatically the selling price.

Private Sub CommandButton1_Click()
Dim wsInvoice As Worksheet, wsRange As Worksheet, wsPrice As Worksheet
Dim nr As Integer, lr As Integer
With ThisWorkbook
     Set wsInvoice = .Worksheets("Invoice")
     Set wsRange = .Worksheets("Range")
     Set wsPrice = .Worksheets("Price")
 End With
nr = wsInvoice.Cells(Rows.Count, 1).End(xlUp).Row + 1
Select Case Me.ComboBox1
Case "Paper"
    wsRange.Range("Paper").Copy wsInvoice.Cells(nr, 1)
    lr = wsInvoice.Cells(Rows.Count, 1).End(xlUp).Row
    For i = nr To lr
        wsInvoice.Cells(i, 2) = Application.VLookup(Cells(i, 1), wsPrice.Range("A:B"), 2, 0)
        wsInvoice.Cells(i, 3) = (".3")
        wsInvoice.Cells(i, 4).Formula = FormatCurrency(wsInvoice.Cells(i, 2).Value / (1 - (wsInvoice.Cells(i, 3))), 2, vbFalse, vbFalse, vbTrue)
    Next i

这里是下载我的文档的链接. https://drive.google.com/file/d/0By_oZy042nKWdTVmX0Fid3JVSHM/edit ?usp =分享

Here is a link to download my document.https://drive.google.com/file/d/0By_oZy042nKWdTVmX0Fid3JVSHM/edit?usp=sharing

推荐答案

我认为这里的FormatCurrency没什么用，可以通过格式化该列一次并将其保留为原来的格式来实现.窗体函数内部的Formula和FormulaLocal似乎存在问题.这是我的解决方法:

I think the FormatCurrency here is a bit useless, this can be accomplished by formatting the column once and leaving it like that. There seems to be an issue with the Formula and FormulaLocal inside form functions. Here's my fix :

删除行wsInvoice.Cells(i,4).Formula ...

在CommandButton1_Click()的末尾，添加此行FormulaCorrection

At the end of the CommandButton1_Click(), add this line FormulaCorrection

在模块内部，写下这个非常简单的函数，该函数将执行您想要的操作:

Inside a module, write down this very simple function that shall do what you want :

Sub FormulaCorrection()

    Sheets("Invoice").Activate
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For x = 2 To lastRow
        Cells(x, 4).FormulaLocal = "=B" & x & "/(1-C" & x & ")"
    Next x

End Sub

这篇关于使用VBA在范围内的依赖单元格上添加公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

1403页，肝出来的..