Function with variable reference

chris1983

New Member
Joined
Mar 23, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
What cells are you trying to put the formula into?
 

chris1983

New Member
Joined
Mar 23, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 23, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows

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
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows

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
Joined
Mar 23, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,127,855
Messages
5,627,269
Members
416,236
Latest member
Lynchbox

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
Top