0

I am trying to turn this formula into VBA code:

=COUNTIFS('Report'!$J:$J,"=In Force")

However, I have acquired the column in the 'Report' file that I will be extracting the data from. The column number it stores in variable ColNum. I need to ensure that it extracts data from the column variable that I have found, and from the 'Report' File.

Here is my code so far (I know it is not correct):

ActiveCell.FormulaR1C1 = "=COUNTIFS(Report'!$I:$I,"=In Force")"

Any help would be greatly appreciated!!!

Improve this question
5
  • 1
    Use this UNTESTED formula. Where FlName and Col are variables. FlName is the name of the file without the Extension and Col is the Column Name. "=COUNTIFS('" & FlName & "'!$" & Col & ":$" & col & ",""=In Force"")" Commented May 19, 2015 at 14:37
  • My apologies in advance for not testing it and posting a proper answer. But I was on my way out when I saw this question popping up. Commented May 19, 2015 at 14:40
  • No worries. Unfortunately I couldn't get it working using my own variables. Commented May 19, 2015 at 14:44
  • Ahh I got it, it was just a problem with my other code. Your's worked! Commented May 19, 2015 at 14:54
  • Awesome :) Btw, in a hurry, i wrote file name. It should be sheetname... Commented May 19, 2015 at 16:11

1 Answer 1

Reset to default
1

If you know the number of the column (in the ColNum variable) that you wish to sum then you can use:

ActiveCell.FormulaR1C1 = "=COUNTIFS('Report'!C" & ColNum & ":C" & ColNum & ",""=In Force"")"

If the name of the sheet is also in a variable, ReportName, then the formula is:

ActiveCell.FormulaR1C1 = "=COUNTIFS('" & ReportName &"'!C" & ColNum & ":C" & ColNum & ",""=In Force"")"
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.