VBA: Mixing Relative & Absolute References

[pcarmour]

Hi,
I have an absolute reference formula that I need to change to always go to the second row ie. A2 G2 W2 wherever it is used instead in this example 67 above.

    ActiveCell.FormulaR1C1 = _        "=SUMPRODUCT(COUNTIF(R[-67]C:R[-1]C,{""HBA1"";""HBA2"";""HBA3"";""HBA4"";""HBA5"";""HBA6"";""HBA7"";""HBA8"";""HBA9"";""HBAF"";""VANT1"";""VANT2"";""VANT3"";""VANT4"";""VANT5"";""BEAUA"";""BEAUM"";""BEAUX""}&""""))"

Any help is always appreciated.

I am working with Windows Home Premium version 6.1.7601 SP 1 Build 7601and Excel version 14.0.6123.5001 (32 bit)

[D_Marcel]

Hi Peter. You may use this:

[VBA]Cells(2, ActiveCell.Column).Value = "=SUMPRODUCT(COUNTIF(R[-1]C:R[-1]C,{""HBA1"";""HBA2"";""HBA3"";""HBA4"";""HBA5"";""HBA6"";""HBA7"";""HBA8""; ""HBA9"";""HBAF"";""VANT1"";""VANT2"";""VANT3"";""VANT4"";""VANT5"";""BEAUA "";""BEAUM"";""BEAUX""}&""""))"[/VBA]

[pcarmour]

Hi D_Marcel,
Thank you for your reply and code. I'm sorry to say I can't get it to work. Please see attachedTest.xlsx.xlsm

[D_Marcel]

Hi Peter,

You're welcome! If I understood your purpose, you need to insert this function always in the second row and in the current column, right? If you're selecting the cell G187, for example, the function should be inserted at cell G2.

Is that right?

[Aflatoon]

I think you mean:
[vba]ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(COUNTIF(R2C:R[-1]C,{""HBA1"";""HBA2"";""HBA3"";""HBA4"";""HBA5"";""HBA6"";""HBA7"";""HBA8""; ""HBA9"";""HBAF"";""VANT1"";""VANT2"";""VANT3"";""VANT4"";""VANT5"";""BEAUA "";""BEAUM"";""BEAUX""}&""""))"
[/vba]

[pcarmour]

HI Aflatoon,
Brilliant exactly as required, Thank you.

D_Marcel, thank you for spending your time on this, as you can see I now have the answer.

[D_Marcel]

Hum, I see. You need to consider always the range from second row of the current column until the previous row. Sorry for not being so helpful.

Thanks Aflatoon.