vba - 如何从代码中删除 Excel VBA 的 $ 符号

Question

我有以下代码:

Sub poorguy()
Dim counter As Long
Dim countto As Long
Dim col1 As String
Dim col2 As String
Dim col3 As String
Dim col4 As String
Dim rngwrite As Range

counter = 0
countto = 100

For i = 0 To countto
    col1 = Cells(12, 2).Offset(0, counter).Address
    col2 = Cells(12, 3).Offset(0, counter).Address
    col3 = Cells(3, 2).Offset(0, counter).Address
    col4 = Cells(3, 3).Offset(0, counter).Address
    Set rngwrite = Cells(58, 3).Offset(counter, counter)
    rngwrite.Value = "=(" & col1 & "-" & col2 & ")*" & col3 & "*" & col4
    counter = counter + 1
Next
Set rngwrite = Nothing
End Sub

问题在于它在公式中生成了太多 $。例如，它给出:=($B$12-$C$12)*$B$3*$C$3

我想要的是:=($B$12-C12)*$B$3*C3

如何删除多余的 $？

Answer 1

将 (false, false) 添加到您不需要绝对值的地址。

Sub poorguy()
Dim counter As Long
Dim countto As Long
Dim col1 As String
Dim col2 As String
Dim col3 As String
Dim col4 As String
Dim rngwrite As Range

counter = 0
countto = 100

For i = 0 To countto
    col1 = Cells(12, 2).Offset(0, counter).Address
    col2 = Cells(12, 3).Offset(0, counter).Address(false, false)
    col3 = Cells(3, 2).Offset(0, counter).Address
    col4 = Cells(3, 3).Offset(0, counter).Address(false, false)
    Set rngwrite = Cells(58, 3).Offset(counter, counter)
    rngwrite.Value = "=(" & col1 & "-" & col2 & ")*" & col3 & "*" & col4
    counter = counter + 1
Next
Set rngwrite = Nothing
End Sub