Put Dimension in formula

Strooprover

New Member
Joined
Jul 21, 2017
Messages
25
Hi All,

Probably a very easy question but I can't find the answer on the internet by myself.
I want to use a formula in different cells and the value has to adjust to the active cell.

This is the relevant part of my formula:
Now i want the R3C to be variable. So i thought up this:
Dim rng1 As Long
Rng1 = ActiveCell.Row

IF(ROWS(R3C:RC)>R1C6

Now I want my formula to work like this:
IF(ROWS(R(Rng1)C:RC)>R1C6

Probably no surprise for you guys but this doesn't work. Can someone show me how this should be written or if this isn't possible show me a approach to get the same result.

Much thanks in advance!

Gr.
Cees
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
I'm not totally clear what you're looking for, but perhaps something like this:

Code:
Dim rng As Range
Dim lRowNo As Long

lRowNo = 7  'say
Set rng = Range("B20:B30")  'say
rng.Formula = "=IF(ROWS(B$" & lRowNo & ":B20)>F$1,""TrueValue"",""FalseValue"")"
 
Upvote 0
Hi,

I'm not able to get your formula working. Maybe I can explain my problem a bit more thoroughly.

Dim rng1 As Long, Contname As String


rng1 = ActiveCell.Offset(2, -1).Row
Contname = ActiveCell.Value


ActiveCell.Offset(2, -1).FormulaArray = "=IF(ROWS(B$" & rng1 & ":B100)>Countif(B$2:B$1000,Contname),"""",INDEX(Sheet1!R2C[1]:R1000C[1],SMALL(IF(Sheet1!R2C1:R1000C1=Contname,ROW(Sheet1!R2C1:R1000C1)-ROW(Sheet1!R2C1)+1),ROWS(B$" & rng1 & ":B100))))"

This is the formula I want to use. However, I keep getting the error: Unable to set the FormulaArray property of the range class.

Can you, or anyone else, help me a bit more?

Thanks in advance!
 
Upvote 0
Your formula contains a mix of A1 and R1C1 references. This should "work":

Code:
ActiveCell.Offset(2, -1).FormulaArray = "=IF(ROWS(R" & rng1 & "C2:R100C2)>Countif(R2C2:R1000C2,Contname),"""",INDEX(Sheet1!R2C[1]:R1000C[1],SMALL(IF(Sheet1!R2C1:R1000C1=Contname,ROW(Sheet1!R2C1:R1000C1)-ROW(Sheet1!R2C1)+1),ROWS(R" & rng1 & "C2:R100C2))))"
... in the sense that it will produce a result, although your formula looks a bit funny, and I'm not sure it will produce the results you're actually looking for.

It would also be better to directly reference the cell (or presumably cells?) for the formula, rather than relying on the position of the ActiveCell, which will make your code susceptible to error.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top