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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,165
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,228
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,098,857
Messages
5,465,102
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top