0

I am using Excel VBA and Rexcel.

Here is the code:

Sub CalcRets()
    Dim outRange As Range
    'ClearOutput
    rinterface.StartRServer

    rinterface.GetRApply "function(mydata)tryingf(mydata)", _
        Range("predict!T17"), AsSimpleDF(DownRightFrom(Range("'database'!B1")))

    rinterface.StopRServer
    Set outRange = Range("predict!T17:CE188").CurrentRegion
    HighLight outRange

    Range("T18:CE188").Select
Selection.Replace What:="#SAKNAS!", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
End Sub

The output is a simple data frame.

The part:

 Range("T18:CE188").Select
    Selection.Replace What:="#SAKNAS!", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

is recorded with Macro recorder. I simply want to replace "#SAKNAS!" with an empty space. But that does not work properly. It does not replace "#SAKNAS!" and sometimes it seem that it also gives a date...

WESECFA.LX.Equity   FFHKOPP.LX.Equity   BALHKGI.FP.Equity
#SAKNAS!                #SAKNAS!                #SAKNAS!
0,072010019             #SAKNAS!                -0,081449492
0,091121495             #SAKNAS!                0,001003906
0,054068522             #SAKNAS!                1900-01-00
-0,047232098        #SAKNAS!                1900-01-00

Best Regards

Community
  • 1
  • 1
user1665355
  • 3,324
  • 8
  • 44
  • 84
  • Hi, I just tried and your code works fine. Could you clarify what is not working properly? Some error message or the result is not as desire? – Alex Mar 10 '14 at 17:00
  • @Alex Hi alex, it does not replace "#SAKNAS!" and there is some issue that I seem to get some dates as well... See my update please. – user1665355 Mar 11 '14 at 08:44
  • The Date is just value zero in date format (format cell), you can copy the format from other cell to fix it. For the replace, 1) Are you running the code in VBA, 2) is #SAKNAS! some key terms (i.e. #N/A equivalent in other language)? – Alex Mar 11 '14 at 15:25
  • @Alex Aha ok! Yes I am running the code in VBA and "#SAKNAS!" is #N/A in swedish:) But i use R now to run my function and it works perfectly. I then export the values with XLConnect... Strange that the same code does not work in VBA with RExcel :) – user1665355 Mar 12 '14 at 10:39
  • Yeah RExcel works a little different, even it's merely an add-in for excel... Glads it works for you now! – Alex Mar 12 '14 at 14:16

0 Answers0