Proper syntax for R1C1 formula in VBA

[hbs1558]

Let's try this again!

I posted this yesterday but it didn't come out right and I did not add code tags because I wasn't sure how...

Anyway, I still would appreciate help!

I am trying to figure out how to make this code below enter a PERCENTILE formula in another cell using variables that change depending on the currently Active Cell

Please Login or Register  to view this content.

If the formula is manually entered on the worksheet at range F11621 as =PERCENTILE(C11621:C18620,0.95) it works as intended...
How should the formula that is highlighted above be coded to use the row and column numbers obtained from the variables?
The first part RC[-3]: works fine but I don't know how to use the right syntax to add the variable for Row2nd which in this instance should be C18060

[rkey]

u can use R[6999]C[-3] for C18620

Please Login or Register  to view this content.

btw, in this case, if you record a macro while filling the formula, it will generate the vba for you

cheers,
Rick

[hbs1558]

Thanks soooo much Rick, it works beautifully...
Now if I only understood why the R[6999] part works???
Thanks again!

[rkey]

....you use the C18620 in your formula, while your question is about C18060 (and my answer mixed them both to )

It works quite simple, just do 11621 + 6999 = 18620. Basicly a positive number brings you a row lower in your sheet, compared to the cell where the fomula is in and a negative one will bring you up.

[hbs1558]

That makes sense now!

Thanks again!!!

[MrShorty]

Quick primer on R1C1 notation:

R[i]c[j]: Brackets are for relative references. This means i rows away and j columns away from this cell. If i is negative, it means above, positive below. Negative j refers to columns to the left, positive j is going right.

RiCj: Without brackets means absolute reference to row i and column j.

So....
RC[-3] -- refers to the cell in the same row and 3 columns to the left.
R[6999]C[-3] -- refers to the cell 6999 rows below and 3 columns to the left.
The same range with absolute references would be R11621C3:R18620C3

[hbs1558]

It's Still not quite right...

The responses given above work but the number (6999) changes or varies depending on the the range size of a particular data set. (and there are numerous data sets in this large worksheet)
That's why I need to use variables to find the beginning and ending row numbers. The total number of rows varies with each data set. So the question still is how can I pass a variable to the formula instead of the highlighted static number 6999 below.
What's the correct syntax?

Please Login or Register  to view this content.

I've tried numerous ways to get the syntax right with no luck, but I'm sure there's a way to do it...

[MrShorty]

The formula is just a text string, so, to use a variable as part of that text string, you simply need to use text manipulation (in particular, the concatenation operator &) to put the text string together. Should be something like:

Please Login or Register  to view this content.