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

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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

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

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
13
Views
254
Replies
2
Views
290
Replies
8
Views
162
Replies
4
Views
383
Replies
2
Views
58

1,128,101
Messages
5,628,693
Members
416,333
Latest member
Time2Learn

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

### Which adblocker are you using?

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

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