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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
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
56,547
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
56,547
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
56,547
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
56,547
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,129,539
Messages
5,636,903
Members
416,949
Latest member
propertyscout

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