COUNTIF formula with reference to a different sheet

0
votes

I would like some help about a difficulty I am facing in creating a countif formula in VBA.

My current formula is the following:

Range("L2:L51").FormulaR1C1 = "=COUNTIF(jj2003__480296.csv!C[-8],Output!RC[-1])"

And it works perfectly. However the range I want to count is always in the same column, but in a constantly changing file. So, for instance the file jj2003__480296.csv tomorrow can have a different name.

In addition, this file is always occupying the last position. so I would like to make the reference flexible.

So far I tried this (without success):

Range("L2:L51").FormulaR1C1 = "=COUNTIF(sheets(sheets.count)C[-8],Output!RC[-1])"
Range("L2:L51").FormulaR1C1 = "=COUNTIF(Worksheets(4)C[-8],Output!RC[-1])"

Any idea on how to make it work?

1
excelvba
The .FormulaR1C1 accepts the string as argument. So you have to concatenate needed vba data into a string first, something like this .FormulaR1C1 = "=COUNTIF(" & sheets(sheets.count).name & "C[-8],Output!RC[-1])" – Vitaliy Prushak
^ Make sure to include a ' before and after the sheet name, and a ! before the column reference. – BigBen
Thanks, but it still not working... Gives back error 468... – G_TTI
.FormulaR1C1 = "=COUNTIF('" & sheets(sheets.count).name & "'!C[-8],Output!RC[-1])" – BigBen
Awesome! thanks a lot! :) also for correctig my text – G_TTI

1 Answers

1
votes

To close this question out:

You need to concatenate the sheet name into the formula. Make sure to include ' before and after the sheet name, and a ! before the column reference:

.FormulaR1C1 = "=COUNTIF('" & Sheets(Sheets.Count).Name & "'!C[-8],Output!RC[-1])"