仅将公式的一小部分转换为相对参考的绝对参考 [英] Convert only a fraction of formula to absolute reference from relative reference

问题描述

首先，如果这是一个愚蠢的问题，我会道歉，但是我只是无法在网上找到任何答案.我有一个问题要问熟悉使用vba将相对公式更改为绝对公式的任何人.

First off, I do apologize if it is a silly question, but I just couldn't find an answer anywhere online. I have a question to ask anyone who is familiar in changing relative formula to absolute formula using vba.

我现在的问题是，我的绝对公式是通过计算其他列(列C)的不同单元格条件的行差来确定的，因为我已经使用vba在列(列B)上输入了多个公式.因此，我想要实现的绝对公式是无法预测的，并且在不断变化.

My problem right now is that, my absolute formula is determined by calculating row difference of different cell conditions of other columns (Column C) as I have input multiple formulas across a column (Column B) using vba. Hence the absolute formula that I want to achieve is unpredictable and it is constantly changing.

   Set Rng = Range(Cells(2, 3), Cells(lastrow, 3))
        Range("B2").Select
        startb = lastrow
        For b = lastrow To 2 Step -1
        If Cells(b, 3) <> Cells(b - 1, 3) Then
        endb = b
        rowdiff = startb - endb
        Cells(end, 2).Select
        ActiveCell.FormulaR1C1 = "=Countif(RC[-1]:R[" & rowdiff & "]C[-1],RC[-1])"
        ActiveCell.Formula = Application.ConvertFormula(Formula:=ActiveCell.Formula, fromreferencestyle:=Application.ReferenceStyle, toabsolute:=xlAbsolute)
        Selection.Copy
        Range(Cells(startb, 2), Cells(endb, 2)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False

        End If
        Next b

你们看到我有一条代码行，可以立即将相对公式转换为绝对公式.不幸的是，在我的COUNTIF公式中，我想要一个绝对范围(逗号前面的部分)，但是要一个相对标准(在这种情况下，逗号后面的RC [-1]).

As you guys can see that I have a code line that can convert relative formula to absolute formula straight away. Unfortunately in my COUNTIF formula, I want an absolute range (the section before the comma) but a relative criteria (in this case the RC[-1] after the comma).

有什么我可以做的只是将部分公式转换为绝对公式?还是没有这种东西?任何帮助和建议，我们将不胜感激.如果我以完全错误的方式进行操作，请告诉我.

Is there anything I could do to just convert a partial formula to become absolute? Or there isn't such thing? Any help and advice is greatly appreciated. Please let me know if I am doing it in a completely wrong way.

感谢您的阅读和时间.

推荐答案

关于部分的转换点，除了最后一个单元格引用外，还可以从相对形式转换为绝对形式，您可以使用此功能

as to your very point of partially converting a formula from relative to absolute except for the last cell reference, you could use this function

Function LeaveLastRelative(strng As String) As String
    LeaveLastRelative = Left(strng, InStrRev(strng, ",")) & Replace(Right(strng, Len(strng) - InStrRev(strng, ",")), "$", "")
End Function

更详细和(可能)清晰的版本是:

whose more verbose and (possibly) clear version is:

Function LeaveLastRelative(strng As String) As String
    Dim strng1 As String, strng2 As String

    strng2 = Right(strng, Len(strng) - InStrRev(strng, ","))
    strng1 = Left(strng, InStrRev(strng, ","))
    LeaveLastRelative = strng1 & Replace(strng2, "$", "")
End Function

用作:

            ActiveCell.FormulaR1C1 = "=Countif(RC[-1]:R[" & rowdiff & "]C[-1],RC[-1])"
            ActiveCell.formula = Application.ConvertFormula(formula:=ActiveCell.formula, fromreferencestyle:=Application.ReferenceStyle, toabsolute:=xlAbsolute)
            ActiveCell.formula = LeaveLastRelative(ActiveCell.formula) '<--| here you use the function

这篇关于仅将公式的一小部分转换为相对参考的绝对参考的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！