偏移公式R1C1每个按钮单击

Question

每次

运行代码时，我想将公式引用从 B15 中的函数向右移动两列。 (我想用它制作一个宏按钮(

'This is the initial reference position: 
Range("B15").FormulaR1C1 = "=(R[-11]C[1]+R[-11]C[3])/(R[-14]C[3]+R[-14]C[1])"
'Next reference position would be this:
Range("B15").FormulaR1C1 = "=(R[-11]C[3]+R[-11]C[5])/(R[-14]C[5]+R[-14]C[3])"

我尝试为 c 值输入变量，但无法多次向前循环。

关于如何构建代码的任何想法？

Answer 1

因为它是一个字符串，你可以遍历每个字符并检查C[知道下一个数字直到]是数字(如果你不想使用全局变量(。 提取它们并添加 2。 这是如何执行此操作的示例

Sub button1_click()
Dim sFormula As String
Dim sParts As String
Dim cChar As String
Dim cFound As Boolean
Dim bracketFound As Boolean
Dim i As Long
cFound = False
bracketFound = False

sParts = Range("B15").FormulaR1C1
'loop through the string
For i = 1 To Len(sParts)
    If cFound = True Then 'did we already find the C in the string
        If bracketFound = True Then 'did we already find the first bracket
            Dim iCount As Long
            iCount = 0 'how many numeric digits we will have
            While IsNumeric(Mid(sParts, i + iCount + 1, 1)) = True 'check each digit after to see if it is numeric
                iCount = iCount + 1
            Wend
            cChar = CStr(CInt(Mid(sParts, i, 1 + iCount)) + 2) 'get our number and add 2
            'update i
            i = i + iCount
            'reset flags
            bracketFound = False
            cFound = False
        Else
            If Mid(sParts, i, 1) = "[" Then 'check for inner bracket
                bracketFound = True
                cChar = Mid(sParts, i, 1)
            End If
        End If
    Else
        If Mid(sParts, i, 1) = "C" Then 'check for the C char
            cFound = True
            cChar = Mid(sParts, i, 1)
        Else
            cChar = Mid(sParts, i, 1)
        End If
    End If
    sFormula = sFormula & cChar 'update formula
Next i
'set the new formula
Range("B15").FormulaR1C1 = sFormula
End Sub