Excel VBA R1C1 formula with variable

[szita2000]

Hi Guys.

I am trying to solve a bad-bad report where I have 135 columns of skills.
I need to filter out people with no skills in any of the skills columns. (124 of them)

So I went with adding an extra column to the end of the table and in the second row I am adding a Countblank formula which is hardcoded in to VBA.
The idea of the formula below is to count the blanks and if it is the same as the skill column numbers (124) Then it is 1, if not (person has any skill) then it is 0.
Then I can filter on to the 1 and 0s

Then they told me that the number of columns is not constant, but they adding new columns to it.

My question is. How can I make the reference dynamic to the columns involved in countblank in the hardcoded R1C1 formula. (RC12:RC135)
And also the number of columns related to it. (124)

What I came up with is that this hardcoded formula is in the last column +1 - 2nd row.
If I would add another formula above the cell with =COLUMN(Pointing to itself second row) -12 . Then I will get the correct number of the width and the number I am trying to compare to then I could reference this first row cell within my R1C1 formula that is hardcoded.

But I still have the issue of not being able to adjust the RC12:RC135 part of my formula within the hardcoded R1C1 formula.
Is there any way to assign a variable within these formulas?

'Find lastrow on main sheet
    lrMain = wsMain.Range("A" & Rows.Count).End(xlUp).Row
    lcMain = wsMain.Cells(1, Columns.Count).End(xlToLeft).Column
   
    'Add in the filter colum to the end
    wsMain.Cells(2, lcMain + 1).FormulaR1C1 = _
      "=IF(AND(COUNTBLANK(RC12:RC135)=124,RC3=""Active""),1,0)"
    Range("EF2").Select
    Selection.AutoFill Destination:=Range("EF2:EF" & lrMain)
   
    Calculate
   
    'Valuing out the Filter column
    Columns("EF:EF").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
    'Getting rid of everything we don't need
    wsMain.Range("B:EE").EntireColumn.Delete

Thanks for any help you can throw at me.

Thomas

 

[Joe4]

Instead of counting blanks, why not count the cells that have values with COUNTA, and check to see if that is 0?
So instead of using this:

    wsMain.Cells(2, lcMain + 1).FormulaR1C1 = _
      "=IF(AND(COUNTBLANK(RC12:RC135)=124,RC3=""Active""),1,0)"

try this, which should be dynamic:

    wsMain.Cells(2, lcMain + 1).FormulaR1C1 = _
       "=IF(AND(COUNTA(RC12:RC[-1])=0,RC3=""Active""),1,0)"

 

[szita2000]

Instead of counting blanks, why not count the cells that have values with COUNTA, and check to see if that is 0?
So instead of using this:

    wsMain.Cells(2, lcMain + 1).FormulaR1C1 = _
      "=IF(AND(COUNTBLANK(RC12:RC135)=124,RC3=""Active""),1,0)"

try this, which should be dynamic:

    wsMain.Cells(2, lcMain + 1).FormulaR1C1 = _
       "=IF(AND(COUNTA(RC12:RC[-1])=0,RC3=""Active""),1,0)"

Oooooh. Thanks Joe4!

But I still have to solve the dynamic question.
How can I make my "hardcoded" COUNTA formula so it will work placed in any column at the end (As I don't know how many columns they will add in future. )
Can I declare a variable as long and count the number of columns then assign with some math assign the correct numbers to the start of the range and end of the range for the COUNTA?

I took a closer look at your formula....
Are those [] brackets signify that something is an offset from the cell the formula in? And the normal numbers like the 12 are absolute references?

 

[szita2000]

Oooooh. Thanks Joe4!

But I still have to solve the dynamic question.
How can I make my "hardcoded" COUNTA formula so it will work placed in any column at the end (As I don't know how many columns they will add in future. )
Can I declare a variable as long and count the number of columns then assign with some math assign the correct numbers to the start of the range and end of the range for the COUNTA?

I took a closer look at your formula....
Are those [] brackets signify that something is an offset from the cell the formula in? And the normal numbers like the 12 are absolute references?

Nevermind! I found it! Thanks for the help.
I need to read up on R1C1 references. :D

Thanks again guys.

 

[Joe4]

Oooooh. Thanks Joe4!

But I still have to solve the dynamic question.
How can I make my "hardcoded" COUNTA formula so it will work placed in any column at the end (As I don't know how many columns they will add in future. )
Can I declare a variable as long and count the number of columns then assign with some math assign the correct numbers to the start of the range and end of the range for the COUNTA?

I took a closer look at your formula....
Are those [] brackets signify that something is an offset from the cell the formula in? And the normal numbers like the 12 are absolute references?

You are welcome!

Yep, exactly. Numbers in brackets are offset from the cell the formula is placed in.
Numbers outside of brackets are hard-coded reference (i.e. C12 is the 12th column, of column L).

But I see it looks like you figured out in the last post!

 

[Jon Peltier]

Nevermind! I found it! Thanks for the help.
I need to read up on R1C1 references. :D

Thanks again guys.

I know it's been solved and all, but maybe it's worth noting that you can use Formula with an A!-style reference, instead of FormulaR1C1 with an R1C1 reference. I know the Macro Recorder always records the R1C1 version of the formula you have just written, but sometimes it's easier, or at least more familiar, to use A1 notation.