Formula not working when entered by VBA

[fali34533]

' finds the last row of column F
LastRow2 = Range("F" & Rows.Count).End(xlUp).Address

'this is the formula I'm trying to enter
ActiveCell.Formula = "=SUMPRODUCT(COUNTIFS(F2:F" & LastRow2 & ",RC[-1]))"

It does go into the cell need by appears with extra marks around the cell reference
=SUMPRODUCT(COUNTIFS('F2':'F6',E8))

Any help will be much apricated.

Thank you

 

[Fluff]

Hi & welcome to MrExcel.
You are mixing A1 & R1C1 notation which is the problem. What cell is the formula going into?

 

[DanteAmor]

Hi @fali34533. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

I have some comments for you:
SUMPRODUCT(COUNTIFS(F2:F" & LastRow2 & ",RC[-1]))
1. You are using 2 different types of cell naming.

2. It should be something like this:

Sub enterformula()
  Dim LastRow2 As Long
 
  LastRow2 = Range("F" & Rows.Count).End(xlUp).Row
  ActiveCell.FormulaR1C1 = "=SUMPRODUCT(COUNTIFS(R2C6:R" & LastRow2 & "C6" & ",RC[-1]))"
End Sub

3. You should use .Row to get the row and not . Address.
LastRow2 = Range("F" & Rows.Count).End(xlUp).Address

LastRow2 = Range("F" & Rows.Count).End(xlUp).Row

4. Your formula does not need sumproduct function. Then:

Sub enterformula()
  Dim LastRow2 As Long
 
  LastRow2 = Range("F" & Rows.Count).End(xlUp).Row
  ActiveCell.FormulaR1C1 = "=COUNTIFS(R2C6:R" & LastRow2 & "C6" & ",RC[-1])"
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[Domenic]

A few things...

1) Return the row number instead of the address to LastRow2 so that you can use it in your formula to specify the row number.

2) Use either the A1 reference style or the R1C1 reference style, but not both.

3) Use ActiveCell.FormulaR1C1 instead of ActiveCell.Formula, if using the R1C1 reference style.

4) Since the criteria contains only a single value, not an array, you can eliminate SUMPRODUCT.

So, for example, if you are going to use the R1C1 reference style, try...

' finds the last row of column F
LastRow2 = Range("F" & Rows.Count).End(xlUp).Row

ActiveCell.FormulaR1C1 = "=COUNTIFS(R2C6:R" & LastRow2 & "C6,RC[-1])"

Hope this helps!

 

[fali34533]

Hi & welcome to MrExcel.
You are mixing A1 & R1C1 notation which is the problem. What cell is the formula going into?

The formula is going into Cell F4.

How do I reference column "F" which holds the data with using A1 notation?

 

[fali34533]

Hi @fali34533. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

I have some comments for you:
SUMPRODUCT(COUNTIFS(F2:F" & LastRow2 & ",RC[-1]))
1. You are using 2 different types of cell naming.

2. It should be something like this:

Sub enterformula()
  Dim LastRow2 As Long
 
  LastRow2 = Range("F" & Rows.Count).End(xlUp).Row
  ActiveCell.FormulaR1C1 = "=SUMPRODUCT(COUNTIFS(R2C6:R" & LastRow2 & "C6" & ",RC[-1]))"
End Sub

3. You should use .Row to get the row and not . Address.
LastRow2 = Range("F" & Rows.Count).End(xlUp).Address

LastRow2 = Range("F" & Rows.Count).End(xlUp).Row

4. Your formula does not need sumproduct function. Then:

Sub enterformula()
  Dim LastRow2 As Long
 
  LastRow2 = Range("F" & Rows.Count).End(xlUp).Row
  ActiveCell.FormulaR1C1 = "=COUNTIFS(R2C6:R" & LastRow2 & "C6" & ",RC[-1])"
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Thank you Dante, that has solved the problem

I really apricate the help

 

[DanteAmor]

I am putting the formula in cell G4 and it counts the values in column F.

For example:

varios 05abr2023.xlsm
	F	G
1
2	d
3	c
4	d	3
5	e
6	d
7	g
KB123 Y

Cell Formulas
Range	Formula
G4	G4	=COUNTIFS(F2:F7,F4)

Then:

Sub enterformula()
  Dim LastRow2 As Long
  
  LastRow2 = Range("F" & Rows.Count).End(xlUp).Row
  Range("G4").Formula = "=COUNTIFS(F2:F" & LastRow2 & ",F4)"
End Sub