+ Reply to Thread
Results 1 to 16 of 16

strange behaviour from vba

  1. #1
    Registered User
    Join Date
    03-24-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    23

    strange behaviour from vba

    hi to you all,

    i have created a vba that will do a currency conversion, to be exact someone on this forum has helped me. Basically on the introduction worksheet you select the country and push on the set button and this correclty format all currency fields into the selected countries currency.

    something however is seriously wrong as when i push on the set button, i get values appearing in non currency fields for some reason.

    Can someone please help and tell me where i have gone wrong,

    For Some reason it was not picking up the first currency field on the worksheet, so i converted the cell to a custom currency field.

    Also i would ideally like the currency simbol to appear in a colum next to the set button, but when i try to add a column it messes up the entire worksheet.

    really appreciate your help,
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    06-02-2015
    Location
    delhi
    MS-Off Ver
    2010
    Posts
    104

    Re: strange behaviour from vba

    I have edited your code a little shorter and without loop !!

    Please Login or Register  to view this content.

    '******************************
    'Made by :- Mandeep baluja
    'https://www.facebook.com/groups/825221420889809/
    'https://www.linkedin.com/in/mandeep-baluja-b777bb88
    Add Reputation if it helps
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-24-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    23

    Re: strange behaviour from vba

    worked like a charm,

    So just so that i can learn something what was the problem with the previous code?

    thank you so much for the help,

    harris

  4. #4
    Registered User
    Join Date
    03-24-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    23

    Re: strange behaviour from vba

    ok this is not working,

    sorry your codes has gone and converted all fields in the excel i nall the worksheets to currency, when only specific fields are currency, some are date, some are string some are just a number.

    can you please help,

    i think thats why the loop existed, it was looking to find a fields that was currency and then transforming it to the selected currency,

    aprreciate your help,

    thanks,

  5. #5
    Forum Contributor
    Join Date
    06-02-2015
    Location
    delhi
    MS-Off Ver
    2010
    Posts
    104

    Re: strange behaviour from vba

    Hey harryv27,

    One thing I would like to know how can I judge which field is need to be formatted as currency and which Not, Basically according to your previous code you're just checking whether the cell is numeric and not blank, Thing I would like to share is Dates are actually number, % are actually numbers. It would be difficult for us to predict which cell need to be formatted as Currency until you confirm us. So that I can find the exact pattern to format them.

  6. #6
    Registered User
    Join Date
    03-24-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    23

    Re: strange behaviour from vba

    Hi Mandeep,
    basically i want to see if the field is numeric, and format = currency then change to the selected currency from the drop down in the introduction tab.

    the previous codes was working for some reason with the loop, but when i tried to insert it into my original excel file it didnt. here is a the file i managed to get this to work on,

    when i move this macro over to the orginal file, it doesnt accept the first currency or leaves it the same, and then it adds junk characters to the bottom of each worksheet right under the table.

    your help is greatly appreciated.

    thanks,

  7. #7
    Forum Contributor
    Join Date
    06-02-2015
    Location
    delhi
    MS-Off Ver
    2010
    Posts
    104

    Re: strange behaviour from vba

    Please find the attached file.I have tried this code more than 50 times, now it is working correctly, thing you should note that this attachment contains a sheet rough which need to be there if you wish You can remove that after doing your work.
    This code will highlight cell in yellow which is formatted in currency format, if that suits your need, comment this line after checking
    y.Interior.Color = vbYellow so that it will not mark yellow color on formatted cells.(Yellow color is just for check for right cells done)


    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-24-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    23

    Re: strange behaviour from vba

    ok you are a super star and this works great,

    only one more thing, when i press on the set button, it take me to the last worksheet nq, how can i get it to just stay on the exisiting introduction worksheet?

    thanks once again for the help,

    Harris

  9. #9
    Forum Contributor
    Join Date
    06-02-2015
    Location
    delhi
    MS-Off Ver
    2010
    Posts
    104

    Re: strange behaviour from vba

    Add these two line at the end of code before end sub

    Intro.activate
    rough.Visible = False

  10. #10
    Registered User
    Join Date
    03-24-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    23

    Re: strange behaviour from vba

    ok perfect that worked lovely.

    another one i noticed is the worksheet st side, there is two columns that are not picking up the macro. they are HW Maint cost/year SW Maint cost/year, any ideas why its not doing these?

    thanks once again

  11. #11
    Forum Contributor
    Join Date
    06-02-2015
    Location
    delhi
    MS-Off Ver
    2010
    Posts
    104

    Re: strange behaviour from vba

    Change this line to this !!

    If rough.Range("A1").Value = "C2" Or rough.Range("A1") = ",2" Or rough.Range("A1") = ",0" Then

  12. #12
    Registered User
    Join Date
    03-24-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    23

    Re: strange behaviour from vba

    thanks so much works like a charm.

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: strange behaviour from vba

    I've been playing around with your sample workbook for a while and have made some changes.

    But first, the reason that you were getting those characters was that part of the old code writes some of the cell formats to other cells lower down the column in order to check the value. I suspect the idea was to check the value and then delete the format ... but, in some cases at least, that wasn't happening.

    I have changed that test so that it uses Application.Evaluate rather than writing to the worksheet. I've also converted the country/currency symbol to a Structured Table and added the Currency Format. I then use that Table for Data Validation and to use VLookup to locate the country and its currency format. The code is a lot shorter as it doesn't use the Select Case construct.

    And I've added a Worksheet Change event handler so that, when you select a country, it writes the currency symbol and the currency format to the sheet. It still uses the Set button to make the change, although that could be incorporated into (called from) the Change event handler.

    Seems to work ok in my testing. Have a play and let me know if it works for you.
    Attached Files Attached Files
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  14. #14
    Registered User
    Join Date
    03-24-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    23

    Re: strange behaviour from vba

    Hi Trevor,

    thanks for the reply and the explanation. works like a charm and seems to be more clean cut than the previous version.

    Thanks for the help

  15. #15
    Registered User
    Join Date
    03-24-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    23

    Re: strange behaviour from vba

    Hi Trevor,

    thanks for the reply and the explanation. works like a charm and seems to be more clean cut than the previous version.

    Thanks for the help

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: strange behaviour from vba

    You're welcome.

    It doesn't need a helper worksheet for the format check.

    Interesting exercise

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Strange Zoom behaviour
    By lgolf in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-27-2014, 09:03 AM
  2. Strange ScrollBar behaviour
    By teylyn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-08-2008, 06:19 PM
  3. Strange Behaviour
    By Mark@Work in forum Excel General
    Replies: 2
    Last Post: 09-30-2008, 12:22 PM
  4. [SOLVED] Strange if(***) behaviour?
    By Excel 2003 - SPB in forum Excel General
    Replies: 6
    Last Post: 08-06-2006, 12:35 PM
  5. Strange VBA Behaviour
    By Ricko in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-28-2005, 03:05 AM
  6. [SOLVED] OLEObjects.Add strange behaviour
    By Pierre Castelain in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-04-2005, 04:05 AM
  7. Strange behaviour
    By Edgar Thoemmes in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-08-2005, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1