1.    20 Oct 2014 #1
    Join Date : Oct 2014
    Posts : 403
    Windows 10 X64

    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:

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

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


    any ideas are welcome

    thanks

    jeff
      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
    Join Date : Oct 2014
    Posts : 403
    Windows 10 X64
    Thread Starter

    hi Superfly,

    will try that ASAP and post back here.

    thanks

    Jeff
      My ComputerSystem Spec
  4.    20 Oct 2014 #4
    Join Date : Oct 2014
    Posts : 403
    Windows 10 X64
    Thread Starter

    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


    jeff
      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
    Join Date : Oct 2014
    Posts : 403
    Windows 10 X64
    Thread Starter

    ok, S will do that

    thanks

    Jef
    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
    Join Date : Oct 2014
    Posts : 403
    Windows 10 X64
    Thread Starter

    ok S

    will check this

    thanks

    Jeff
      My ComputerSystem Spec
  9.    20 Oct 2014 #9
    Join Date : Oct 2014
    Posts : 403
    Windows 10 X64
    Thread Starter

    ok Superfly,

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


    Jeff
      My ComputerSystem Spec
  10.    20 Oct 2014 #10

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

 


Similar Threads
Thread Forum
Some new Python/C# code..
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:
Chillout Room
Solved Error code 0XC000021a "Keyboard & Mouse stopped working"
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...
Drivers and Hardware
Hour of Code
Read more... https://www.youtube.com/watch?v=JDw1ii7aKwg
Windows 10 News
Solved I'm not getting my code from Microsoft!
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...
Browsers and Email
Solved Finally I got a code!!!
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...
General Support
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 07:10.
Find Us
Twitter Facebook Google+ Ten Forums iOS App Ten Forums Android App



Windows 10 Forums