# Function with variable reference

#### chris1983

##### New Member
Hi everybody, I am very glad to join you. I have recently started to use vba and so far i am very thrilled about it.
I am trying to a enter a function and i am facing the following problem:

Dim u As Integer
For u = 2 To t - 1 'I calculated the value of variable t earlier in my code
Cells(8, u).Select 'There are two worksheets, Sheet1 and Sheet2. The active sheet is Sheet2
ActiveCell.Formula = "=SUM(--(FREQUENCY(IF(Sheet1!BI2:BI10000=b2,MATCH(Sheet1!q2:q10000,Sheet1!q2:q10000,0)),ROW(Sheet1!q2:q10000)-ROW(Sheet1!q2)+1)>0))"
Next u

The formula itself works fine. However
b2 value is only correct for u=2. I tried to use cells(2,u) instead of b2 but it didnt work.

The same problem occured with the lower limit of the range where i use 100000 instead of the variable k i already have calculated and used erlier.

Do i have a syntax mistake or is something else?
Any help would be great.

Thank you in advance

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
What cells are you trying to put the formula into?

#### chris1983

##### New Member
cells are you trying to put the formula into?
I am trying to put the formula to B8, C8, D8 until the column is t-1, lets say E8.
Each time i need for calculation B2, C2, D2 etc from the same Sheet (Sheet 2) and some other cells from Sheet 1.

#### Fluff

##### MrExcel MVP, Moderator
Ok, in that case do you want the columns BI & Q to be static, or should they change for column the formula is in?

#### chris1983

##### New Member

ADVERTISEMENT

Ok, in that case do you want the columns BI & Q to be static, or should they change for column the formula is in?
The columns BI and Q are static. Also static is their upper row limit which is 2. The only thing that changes is the lower limit. I had to calculate that earlier in my code, and i used "k". I had 100000 in the code just to overcome the problem and check the formula

#### Fluff

##### MrExcel MVP, Moderator
Ok, thanks for that. How about
VBA Code:
``````   Range("B8").FormulaArray = "=SUM(--(FREQUENCY(IF(Sheet1!\$BI\$2:\$BI\$10000=b2,MATCH(Sheet1!\$q\$2:\$q\$10000,Sheet1!\$q\$2:\$q\$10000,0)),ROW(Sheet1!\$q\$2:\$q\$10000)-ROW(Sheet1!\$q\$2)+1)>0))"
Range("B8").Resize(, t - 1).FillRight``````

#### Fluff

##### MrExcel MVP, Moderator

ADVERTISEMENT

And to use the variable k
VBA Code:
``````   Range("B8").FormulaArray = "=SUM(--(FREQUENCY(IF(Sheet1!\$BI\$2:\$BI\$" & k & "=b2,MATCH(Sheet1!\$q\$2:\$q\$" & k & ",Sheet1!\$q\$2:\$q\$" & k & ",0)),ROW(Sheet1!\$q\$2:\$q\$" & k & ")-ROW(Sheet1!\$q\$2)+1)>0))"
Range("B8").Resize(, t - 1).FillRight``````

#### chris1983

##### New Member
Ok, thanks for that. How about
VBA Code:
``````   Range("B8").FormulaArray = "=SUM(--(FREQUENCY(IF(Sheet1!\$BI\$2:\$BI\$10000=b2,MATCH(Sheet1!\$q\$2:\$q\$10000,Sheet1!\$q\$2:\$q\$10000,0)),ROW(Sheet1!\$q\$2:\$q\$10000)-ROW(Sheet1!\$q\$2)+1)>0))"
Range("B8").Resize(, t - 1).FillRight``````
It works fine!! Thank you very much!!!!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

Replies
0
Views
74
Replies
13
Views
312
Replies
2
Views
308
Replies
0
Views
61
Replies
8
Views
254

Threads
1,130,163
Messages
5,640,507
Members
417,148
Latest member
pe3087te

### 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

### 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