Writing a worksheetFunction to the worksheet

Rasm

Well-known Member
Joined
Feb 9, 2011
Messages
500
I m trying to populate a cell with the text


=STEYX(J13:J26;K13:K26)

My code below writes that into the Avar (Dimmed as a Variant)



Avar = "=STEYX(" & Split(Cells(1, i).Address, "$")(1) & Trim(Str(FirstRowResult)) & ":" & Split(Cells(1, i).Address, "$")(1) & Trim(Str(LastRowResult)) & ";" & Split(Cells(1, ii).Address, "$")(1) & Trim(Str(FirstRowResult)) & ":" & Split(Cells(1, ii).Address, "$")(1) & Trim(Str(LastRowResult)) & ")"



The code below - gives an error

.Cells(Along, ii) = Avar


If I use the code blow- it works - but only the calculated value is shown - not how it is calculated (I want it to show what range is used) - I have populated the two arrays with the values from row 13 to 26 for column K & L

Application.WorksheetFunction.StEyx(RefVal, PredVal)
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,207
What is the resulting string assigned to Avar?

Also, does your version of Excel use a semi-colon as a list separator, as per your example? Or does it actually use a comma as a list separator?
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,291
Office Version
2010
Platform
Windows
The way I solve problems with writing formula into cells is to remove the "=" from the front of the formula so that you just write Text. then go to the cell and manually put the "=" back in , EXCEL then very kindly highlights where the error is.
 

Rasm

Well-known Member
Joined
Feb 9, 2011
Messages
500
What is the resulting string assigned to Avar?

Also, does your version of Excel use a semi-colon as a list separator, as per your example? Or does it actually use a comma as a list separator?


Avar=
=STEYX(J13:J26;K13:K26)

If I add a ' (remark) - then goto to the cell where it wrote

'=STEYX(J13:J26;K13:K26)

and remove the ' ---- then it works



 

Watch MrExcel Video

Forum statistics

Threads
1,102,439
Messages
5,486,897
Members
407,570
Latest member
cannotquitexcel

This Week's Hot Topics

Top