Windows 10: Question about VBA CODE not working good Solved

  1.    20 Oct 2014 #1

    Question about VBA CODE not working good

    hi guys,

    do not know if there are VBA specialists on the board, but I pop the question:

    since recently when I save a docu automatically, like 199-invoice I get:

    blanc 199-invoice, instead of the same without the blanc position in front. Sorting the invoice at the back, since sort happens decending.

    When I remove the blanc in front the sort happens correctly, but when I rename the invoice to 199-invoic (not altering the blanc position) all of a sudden the sort is ok as well.

    Very strange.

    In order to remove all macro's and codes to get a blanc document I use:

    With ActiveSheet.UsedRange
    .PasteSpecial xlValues
    .PasteSpecial xlFormats
    End With

    This happens before the save and my guess is that the problem lies there.

    any ideas are welcome


      My ComputerSystem Spec

  2.    20 Oct 2014 #2

    I'm no VBA specialist but seems it's a formatting issue - if what you mean by "blanc" is a leading space?
    In normal code Trim remove those, but in VBA I suppose one of the constants does that - maybe try ..

    .PasteSpecial Paste:=xlPasteValues

    Not sure why removing he last char has a different output to the sort function though...
      My ComputerSystem Spec

  3.    20 Oct 2014 #3

    hi Superfly,

    will try that ASAP and post back here.


      My ComputerSystem Spec

  4.    20 Oct 2014 #4

    well Superfly,

    no difference, do not know what to do with it.

    result just the same, when renaming it to whatever, the space in the first position is gone then.

    but thanks

      My ComputerSystem Spec

  5.    20 Oct 2014 #5

    OK PM me the Excel file if you would like me to have a look at how the code hangs together.

    It's a bit off topic here so I may reply via PM instead.
      My ComputerSystem Spec

  6.    20 Oct 2014 #6

    ok, S will do that


    Last edited by jeffrys; 21 Oct 2014 at 04:35.
      My ComputerSystem Spec

  7.    20 Oct 2014 #7

    Jeff, I have replied to your post with what I think is the solution ... it's a common mistake made when concatenating strings to form a file path.

    Hope it works.
      My ComputerSystem Spec

  8.    20 Oct 2014 #8

    ok S

    will check this


      My ComputerSystem Spec

  9.    20 Oct 2014 #9

    ok Superfly,

    you where right and it works. Thanks for your help, you saved my week....

      My ComputerSystem Spec

  10.    20 Oct 2014 #10

    You're welcome - happy to be of assistance :)
      My ComputerSystem Spec


Related Threads
Some new Python/C# code.. in Chillout Room
Python: Convert a decimal value to a roman numeral value: This is a version to test the code with random number 1 to 1,200 and for ten tests:
My win 10 computer has just started giving me the above error code. I can log in using mouse & keyboard, but when I get to the start screen I get the above error code 0XC000021a. I have tried several times to do a fix, like shutting off the...
Hour of Code in Windows 10 News
Hi folks! I would like to switch my PC to a Microsoft account and not a local one. However, to do this I must first enter a code which Microsoft is suppose to send me via email. But I'm not getting the code! I know I'm putting in the right...
Solved Finally I got a code!!! in General Support
Hi Ever since I installed I've been trying to get the authentication code thing. Finally today on what has to be at least the 20th try I received a code. I have virtually all of my software installed now, so I feel that the setup process is...
Our Sites
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd
All times are GMT -5. The time now is 14:39.
Find Us