COUNTIF FormulaR1C1 with calculated relative position

Question

BACKGROUND

I have a script which inserts a column after every existing column in a table which needs to insert a COUNTIF formula into it where the range and criteria are based upon two different loop variables.

I can make an R1C1 formula work fine, for the first one but since the range and criteria are not proportional to their position, R -1 and C -1 do not work in my formula.

SCRIPT PURPOSE

Here's the script with the COUNTIF as a FormulaR1C1 which I need to change.

The first part of the script loops taking data from each column of from sheet 'origin_data' and puts it into the same column in sheet 'origin_analytics' and removes duplicate values in the process.

The second part of the process has a loop to setup a COUNTIF column to be inserted after every column in sheet 'origin_analytics'. It sets up value x as the origin_data column number (increment by 1 each time) and has y as the origin_analytics sheet (increments by 2 each time to factor in the previously inserted column).

When I create the formula I enter into row 6 column y + 1. The formula is COUNTIF(x:x, y). When it finishes, it formats and AutoFill down to last row (set by origin_data RowCount earlier in the sub).

The last version of the COUNTIF I tried unsuccessfully was:

s2.Cells(6, y + 1) = WorksheetFunction.CountIf(Sheets("origin_data").Range(Sheets‌("origin_data").Cell‌s(1, x), Sheets("origin_data").Cells(Rows.Count, x)), Sheets("origin_analytics").Cells(6, y))

THE ISSUE

The error message I get is run-time error '9' Subscript out of range which I believe happens because COUNTIF in VBA doesn't like to work unless in a FormulaR1C1. Is it possible to have one of those that includes the use of variables?

PERCEIVED SOLUTION

If I could set the range from R -1 (relative to my current position) to result of (x - y) - 1.

Examples of Formula R and C combination as it loops through:

Formula in B, R (origin_analytics!) = -1 (A), C (origin_analytics!) = -1 (A)

Formula in D, R (origin_analytics!) = -2 (B), C (origin_analytics!) = -1 (C)

Formula in F, R (origin_analytics!) = -3 (C), C (origin_analytics!) = -1 (E)

Formula in H, R (origin_analytics!) = -4 (D), C (origin_analytics!) = -1 (G)

THE CODE

'For each column, copy distinct values and paste into origin_analysis sheet
  Dim s1 As Worksheet, s2 As Worksheet, x As Long, y As Long
  x = 1
  Do Until x = columnCount
    Set s1 = Sheets("origin_data")
    Set s2 = Sheets("origin_analysis")
    s1.Columns(x).Copy s2.Columns(x)
    s2.Columns(x).RemoveDuplicates Columns:=1, Header:=xlNo

    x = x + 1
 Loop

'Insert column after every column and add a COUNTIF calculation for every distinct value from origin_raw
    x = 1
    y = 1
 Do Until x = columnCount
    s2.Columns(y + 1).Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.ColumnWidth = 3.29
    Cells(6, y + 1).Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(origin_data!C[-1],origin_analysis!RC[-1])"
    Cells(6, y + 1).Select
    Selection.FormatConditions.AddDatabar
    Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
        .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
    End With
    With Selection.FormatConditions(1).BarColor
        .Color = 2668287
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).BarFillType = xlDataBarFillSolid
    Selection.FormatConditions(1).Direction = xlContext
    Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
    Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
    Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
    With Selection.FormatConditions(1).AxisColor
        .Color = 0
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).NegativeBarFormat.Color
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.AutoFill Destination:=Range(Cells(6, y + 1), Cells(RowCount, y + 1))

    x = x + 1
    y = y + 2
 Loop

Matt Lightbourn Matt Lightbourn · Accepted Answer · 2020-03-03T23:24:37

I thought I would post the answer which I accidentially stumbled upon which works, in case it's of any interest to anyone else.

ActiveCell.FormulaR1C1 = "=COUNTIF(origin_data!C[" & x - y - 1 & "],origin_analysis!RC[-1])"

Now it works a treat! Thanks Scott Craner for your earlier help, much appreciated

COUNTIF FormulaR1C1 with calculated relative position

1 Answers