如何在复杂的公式中添加美元符号? [英] How to add a Dollar $ sign to a complex formula?

问题描述

我已经为我正在工作的这段代码问了几个问题，并在另一篇文章中(这里:)，JvdV给了我这个公式:

I already asked several questions for this code I'm writing for work, and in another post ( here : How to insert, through VBA, a formula in a cell with special characters like "-" or "$"? ), JvdV gave me that formula :

With Worksheets(LabourSheet)
    .Cells(PosStartLineCalc + 1, PosStartColumnLt).Formula = "=IF(or(" & .Cells(PosStartLineCalc, PosStartColumnLt).Address(False, False) & "=""-""," & .Cells(PosStartLineCalc, PosStartColumnLt).Address(False, False) & "=""X""),""-"",Cars!" & .Cells(PosStartLine + (TPICode * 3) - 3, Split(Cells(1, col).Address, "$")(1)).Address(False, False) & "*" & .Cells(PosStartLineCalc, PosStartColumnLt).Address(False, False) & "*Data!$C$8)"
End With

在单元格中给出一次，例如:= IF(OR(C4 =-"; C4 ="X");-"; Cars！C4 * C4 * Data！$ C $ 8)

Which once in the cells give, for example : =IF(OR(C4="-";C4="X");"-";Cars!C4*C4*Data!$C$8)

如果我告诉您我不完全了解VBA方面，这不足为奇...

No surprise if I tell you that I don't understand the VBA side fully...

无论如何，它可以工作，但是当我复制通过整个表格时，我需要添加一些"$"美元符号，并且"Cars！C4"应保持原样...

Anyway, it works, but I would need to add some "$" dollar signs as I copy past the whole table and the "Cars!C4" should stay as it is...

那么，如何在"Cars！C4"上添加漂亮的美元符号成为"Cars！$ C $ 4"?

So, how to add nice dollar signs on the "Cars!C4" to become "Cars!$C$4"?

我这样尝试过:

，汽车！" & .Cells("$"& PosStartLine +(TPICode * 3)-3，"$"& Split(Cells(1，col).Address，"$")(1)).Address(False，False)

",Cars!" & .Cells("$" & PosStartLine + (TPICode * 3) - 3, "$" & Split(Cells(1, col).Address, "$")(1)).Address(False, False)

，汽车！" & .Cells(" $"& PosStartLine +(TPICode * 3)-3，" $"& Split(Cells(1，col).Address，"$")(1)).Address(False，False)

",Cars!" & .Cells(""$"" & PosStartLine + (TPICode * 3) - 3, ""$"" & Split(Cells(1, col).Address, "$")(1)).Address(False, False)

，汽车！" & .Cells( chr(36)& PosStartLine +(TPICode * 3)-3， chr(36)& Split(Cells(1，col).Address，"$")(1)).Address(False，False)

",Cars!" & .Cells(chr(36) & PosStartLine + (TPICode * 3) - 3, chr(36) & Split(Cells(1, col).Address, "$")(1)).Address(False, False)

它给我一个类型不匹配"的错误:'(

it gives me a "type mismatch" error :'(

什么都没做...

我当然在互联网上搜索过，但是我总是在页面上找到他们解释说，您可以按F4键以及美元符号是什么...

I searched on internet, of course, but I always find pages where they explain that you can press F4 and for what the dollar signs are...

先谢谢了！

推荐答案

.Cells(PosStartLine + (TPICode * 3) - 3, Split(Cells(1, col).Address, "$")(1)).Address(False, False)

在这段代码中，将赋予.address的参数更改为(True，True)

In this piece of code, change the arguments given to .address to (True,True)

.Cells(PosStartLine + (TPICode * 3) - 3, Split(Cells(1, col).Address, "$")(1)).Address(**True, True**)

这篇关于如何在复杂的公式中添加美元符号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！