PDA

View Full Version : [SOLVED] VBA: Mixing Relative & Absolute References



pcarmour
01-14-2014, 03:08 PM
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
01-14-2014, 06:59 PM
Hi Peter. You may use this:

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""}&""""))"

pcarmour
01-15-2014, 12:51 AM
Hi D_Marcel,
Thank you for your reply and code. I'm sorry to say I can't get it to work. Please see attached11094

D_Marcel
01-15-2014, 06:06 AM
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
01-15-2014, 06:06 AM
I think you mean:
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""}&""""))"

pcarmour
01-15-2014, 06:48 AM
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
01-15-2014, 07:51 AM
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.