Writing a worksheetFunction to the worksheet

[Rasm]

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)

 

[Domenic]

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]

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]

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​

​