Hi,
I have var1 with value
=LAJITTELE(A600:I627;3)
I put it to active Excel cell with
xl.ActiveCell.Value := Var1
After that cell value is
=@LAJITTELE(A600:I627;3)
So it adds extra @ mark. Why and how to avoid that?
Extra @ mark
Re: Extra @ mark
It's because your formula returns multiple values, and that's how newer versions of Excel indicate it in the formula. More info here.
Re: Extra @ mark
OK but the problem is that function does not work with that mark. If there is no way to avoid this I have to do this another way. Maybe using clipboard.
- flyingDman
- Posts: 2791
- Joined: 29 Sep 2013, 19:01
Re: Extra @ mark
I am not fluent in Finnish Excel but... if LAJITTELE is the same as SORT, use =LAJITTELE(A600:I627,3) (that is , rather than ; ), then do not use .Value but use .formula2. So something like this:
Code: Select all
xl := ComObjActive("excel.application")
xl.activesheet.range("X1").formula2 := "=LAJITTELE(A600:I627,3)"
14.3 & 1.3.7
Re: Extra @ mark
I'm thinking the ; is used as the parameter separator in that locale because the , is used as the decimal separator, as it often is in Europe.flyingDman wrote: ↑ use =LAJITTELE(A600:I627,3) (that is , rather than ; )
- flyingDman
- Posts: 2791
- Joined: 29 Sep 2013, 19:01
Re: Extra @ mark
I had seen an example here: https://support.microsoft.com/fi-fi/office/lajittele-funktio-22f63bd0-ccc8-492f-953d-c20e8e44b86c
14.3 & 1.3.7
Re: Extra @ mark
Thankyou. Using Formula2 instead of value solved the problem.
Yes, LAJITTELE is SORT. I would prefer use English formulas but our company uses Finnish version. And we are using semicolon as formula separator.
Yes, LAJITTELE is SORT. I would prefer use English formulas but our company uses Finnish version. And we are using semicolon as formula separator.