Function with variable reference

[chris1983]

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

 

[Fluff]

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

 

[chris1983]

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]

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]

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]

Ok, thanks for that. How about

   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]

And to use the variable k

   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]

Ok, thanks for that. How about

   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]

You're welcome & thanks for the feedback