Excel-Visual Basic-Variable Row?

Question

I have a column of 1's or 100's or blanks

If I am at J154, then T=153 (do not worry that they are the same, first cell is title)

I first will go to J155 and I want to count the 100's above it

ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1]C:R[-T]C,""100"")"

Ideally in excel the cell J155 will look like, "=COUNTIF(J2:J154,100)
BUT I cannot use "T" in the formula input.

How do I use a relative reference, "R[-1]C" and an absolute reference, "J2" in the same formula. OR, how do I input a variable, "T", into the formula? Any assistance would be greatly appreciated.

Answer 1

The FormulaR1C1 is a text string posted into Active cell

SO

Simple answer is

ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1]C:R[-" & T & "]C,""100"")"



And regarding absolute and related references, I am not sure if I got you well here, but note that



R4C4 will do $D$4

R5C[4] will do $D4

R[5]C4 will do D$4

and so one



Yes, VBAXLMan is here to feed your Excel needs

Answer 2

I think this is what you're trying to do:



ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.Formula = "=COUNTIF(J2:J154,100)"



The second line just tells the macro to enter the formula =COUNTIF(J2:J154,100) into the active cell.



Basically if you have 45 100's listed in cells J2:J154, and if you're active cell is J154 when you run the macro, cell J155 will display 45 as the formula =COUNTIF(J2:J154,100) will count 45 100's.

Answer 3

it truly is beneficial to think approximately coming up an "money owed" database to save each gamers maximum cutting-edge stats, even in spite of if or not they are online precise now or not. you won't be able to assume that every time you flow to play, each participant is going to be accessible so which you won't be able to without delay regenerate each participant the place they left off. they may well be ill that day. i think of you're purely speaking some 2 dimensional array, saving it as a report.