Avoid Using R1C1 notation in VBA

[Mitchbvi]

I am trying to work out how I can replace RICI notation with names in my worksheet in a formula array . This works.

Range("M3").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Expense2, MATCH(0,IF(ISBLANK(Expense2),1,COUNTIF(R2C13:R[-1]C, Expense2)), 0)),"""")"
    Selection.AutoFill Destination:=Range("M3:M14"), Type:=xlFillDefault
    Range("M3:M14").Copy
        Selection.PasteSpecial Paste:=xlValues

The formula in M3 is

=IFERROR(INDEX(Expense2, MATCH(0,IF(ISBLANK(Expense2),1,COUNTIF($M$2:M2, Expense2)), 0)),"")


What I want to do is have the count function use names like this.

Range("M3").Select
FirstArr = ActiveCell.Offset(-1, 0).Address
Fixrow = ActiveCell.Row
FixCol = ActiveCell.Column
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Expense2, MATCH(0,IF(ISBLANK(Expense2),1,CountIf (FirstArr:R[-1]C, Expense2)), 0)),"""")"

The code does not throw up any errors this it what it enters in M3

=IFERROR(INDEX(Expense2, MATCH(0,IF(ISBLANK(Expense2),1,CountIf (FirstArr:M2, Expense2)), 0)),"")

I have tried making FirtsArr a range Reference with square brackets that throws an error. I need to understand if one can mix R1C1 notation with names and no amount of searching has produced a result.

Thanks

 

[pella88]

Hi,

Can you tell what exactly are you trying to achieve? Based on your code, FirstArr = M2, so your formula would be M2:M2?

In any case, if ":M2" is always the same, I see no reason to use R[-1]C... Maybe it would be easier to find the full range within VBA and directly use it in function.

 

[Mitchbvi]

Hi,

Can you tell what exactly are you trying to achieve? Based on your code, FirstArr = M2, so your formula would be M2:M2?

In any case, if ":M2" is always the same, I see no reason to use R[-1]C... Maybe it would be easier to find the full range within VBA and directly use it in function.

Thanks for getting back to me you are nearly correct but the formula shown in the one that works below is $M$2:M2. Also it is copied down for a number of rows, I have posted the entire sub below as that may make it easier to explain. Interestingly I tried changing the R1C1 reference to $M$2:M2 and that did not work.

Sub UniqueExpenses()
'Gets all unique names in the Expenses column Pastes to the designated column removes formulas and tranpose to Row
'Check to delete unnecessary columns
Dim FirstArr As Variant
Range("M3").Select
'The following works with the countif function and always refers to Row 2 Col 13 ("M") needs to change so it is dynamic
    Selection.FormulaArray = _
        "=IFERROR(INDEX(Expense2, MATCH(0,IF(ISBLANK(Expense2),1,COUNTIF(R2C13:R[-1]C, Expense2)), 0)),"""")"
    Selection.AutoFill Destination:=Range("M3:M14"), Type:=xlFillDefault
    Range("M3:M14").Copy
        Selection.PasteSpecial Paste:=xlValues
    Range("O2").Select
     Selection.PasteSpecial Paste:=xlAll, SkipBlanks:=True, Transpose:=True
     Range("L:N").Delete
    Range("P4").Select
    ActiveWorkbook.Save
    ActiveWorkbook.Save
    Columns("J:R").Select
    Selection.Delete shift:=xlToLeft
End Sub

Thanks for taking the trouble to look.

Peter

 

[Michael M]

Untested....

Sub UniqueExpenses()
'Gets all unique names in the Expenses column Pastes to the designated column removes formulas and tranpose to Row
'Check to delete unnecessary columns
Dim FirstArr As Variant
With Range("M3:M14")
.FormulaArray = "=IFERROR(INDEX(Expense2, MATCH(0,IF(ISBLANK(Expense2),1,COUNTIF($M$2:M2, Expense2)), 0)),"")"
.Value = .Value
End With
    Range("O2").Select
     Selection.PasteSpecial Paste:=xlAll, SkipBlanks:=True, Transpose:=True
     Range("L:N").Delete
    Range("P4").Select
    ActiveWorkbook.Save
    ActiveWorkbook.Save
    Columns("J:R").Select
    Selection.Delete shift:=xlToLeft
End Sub

 

[Mitchbvi]

Untested....

Sub UniqueExpenses()
'Gets all unique names in the Expenses column Pastes to the designated column removes formulas and tranpose to Row
'Check to delete unnecessary columns
Dim FirstArr As Variant
With Range("M3:M14")
.FormulaArray = "=IFERROR(INDEX(Expense2, MATCH(0,IF(ISBLANK(Expense2),1,COUNTIF($M$2:M2, Expense2)), 0)),"")"
.Value = .Value
End With
    Range("O2").Select
     Selection.PasteSpecial Paste:=xlAll, SkipBlanks:=True, Transpose:=True
     Range("L:N").Delete
    Range("P4").Select
    ActiveWorkbook.Save
    ActiveWorkbook.Save
    Columns("J:R").Select
    Selection.Delete shift:=xlToLeft
End Sub

Michael thanks very much I guess I did not make myself very clear. I did get COUNTIF($M$2:M2) working I had left a space in place. However what I want to do is replace the $M$2:$M2 with Names. the same is true with the With Range in your example but I'm sure I can fix that.

I am doing something wrong when I replace the $M$2:M2 address . Did try assigning the absolute and relative address to a variable and the worked for the 1st Row but of course each row after had the same absolute and variable address so returned the same answer. It works in R1C1 notation as the M relative increases as the formula is copied down

Set CellCheck = Application.InputBox("Select 1st Cell to Put Expenses names in", Type:=8)
CellCheck.Select
FirstArr = CellCheck.Address
SecArr = CellCheck.Address(0, 0)
Arr1 = FirstArr & ":" & SecArr
Selection.FormulaArray = "=IFERROR(INDEX(Expense2, MATCH(0,IF(ISBLANK(Expense2),1,COUNTIF(ARR1, Expense2)), 0)),"""")"

Tried using

Selection.FormulaArray = "=IFERROR(INDEX(Expense2, MATCH(0,IF(ISBLANK(Expense2),1,COUNTIF(Firstarr:secarr, Expense2)), 0)),"""")"

That simple enters the string FirstArr:SecArr as part of the formula , tried putting address after the two variables that gives me runtime error .

Tried with double quotes concatenating the absolute and relative address that produces a runtime error unable to set the FormulaArray property of the Range Class

    Selection.FormulaArray = "=IFERROR(INDEX(Expense2, MATCH(0,IF(ISBLANK(Expense2),1,COUNTIF("" & Firstarr:secarr & "", Expense2)), 0)),"""")"

Thanks for trying.

Peter

 

[MARK858]

Untested...

.FormulaArray = "=IFERROR(INDEX(Expense2, MATCH(0,IF(ISBLANK(Expense2),1,COUNTIF([COLOR="#FF0000"]" & Range("YourNamedRange") & ", [/COLOR]Expense2)), 0)),"")"

 

[Mitchbvi]

Untested...

.FormulaArray = "=IFERROR(INDEX(Expense2, MATCH(0,IF(ISBLANK(Expense2),1,COUNTIF([COLOR=#FF0000]" & Range("YourNamedRange") & ", [/COLOR]Expense2)), 0)),"")"

Thanks I finally got there.