• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA for if statement based on number format selection

OK i found something in Mozilla Firefox i can not upload excel file don't know why but with explorer i can so i will send it trough there
 
Ok I upload some of the file , hope it will be Ok , I also place the code in the module , what ells I like you to help me if you can is that I have some number in every sheet, and I like those number to be change when I select the currency , thank you again , there are no word to appreciate your help looking forward to your replay
Mike
 

Attachments

  • FACS-MIKE -fileupload.xls
    828 KB · Views: 0
Hi ,

See this file ; I have used Activesheet instead of Worksheets("Data Input Area") ; you need to activate this tab before running the macro.

Narayan
 

Attachments

  • FACS-MIKE -fileupload.xls
    825.5 KB · Views: 0
ok i see that , i am away from my desk i will get back to my desk and try it , i thank you so much you can not understand how much you help me ,
 
Hi man , i sorry for the delay in returning back to you , the internet server was on maintenance and i was out of connection, any way just to tell you i tried that and it's working you are awesome , many many thanks to you for the time and the patient that you show , hope to see more good guys like you , I might have some more things I will need help that I will have the pleasure to use your knowledge, just one question do you also provide assistance for big projects from start to end ????



again thank you for everything
 
i am working with the file but there is one thing to be fixed , when i run the macro it change also number that in general format (i.e like the year , or numbers of answer i got from employees) which i don't want them to be change how it can be done? i mean like you done for the percentage that if it's format percentage no change should take place in with this format, you can see that at the file you sent me the line on row 8 is the year , and when we run the macro it take the currency symbol.
i also pay attention that if the number is 0 it does not get the currency symbol, can you tell me why?
 
Last edited:
Hi ,

Can we assume that when ever you run the macro , what ever cell needs its format changed will satisfy the following rules :

1. If the value is between -1 and +1 , we can assume that it is a percentage value , and will need to be formatted as a percentage

2. If the value is beyond these 2 values , then , if it already has a currency symbol in its format string , then it will be formatted using the selected currency symbol , otherwise it will be left as it is.

Narayan
 
Hi
look I tell you where is my problems if you already pay attention the file I sent you it's a data from company's financial statement , when from my experience most of the time there are going to be some rubricates that will have a value and some no , (number will be 0) therefore I am not sure if your suggestion will hold in that case, I also joined the file to your attention , please look on range Q49:R68 , as you can see the number take the currency symbol when it should be just a number without any symbol, moreover , I also pay attention now to something ells , if you look range P49: P68 , it does not get the symbol and that ok , and the reason because it is formatted as text , is there is any way how to integrate that sort of format in the macro? that will resolve the matter
 
Hi ,

There is no point in asking me ; you know your data better than I do ; you know what are all the variations present in your data , and you know which data is to be reformatted and which is to be left as it is.

If you ask me , the only fool-proof solution would be if you select the range which is to be formatted and then run the macro ; the macro will format all numbers that are present in the selected range.

Of course , if your data is over many columns and rows , this may not be feasible , but I cannot think of any other method which will be fool-proof. If you can suggest , I can see how it can be implemented.

Narayan
 
I agree with you but because it is long data as you know from 9 to 6000 I don't know what I can do to make it work I thought maybe you can help me to integrate in the macro some "if" conditions that will determine the data with the currency symbol and the rest of the data that are general number and percentage
 
Hi ,

Can you ensure that all cells which need to be formatted will be custom formatted , while all other cells are formatted as General ?

If this is done , then the code can check this and do the appropriate formatting.

Narayan
 
hi firstly i would like to thank you, moreover , i run your last macro , it working fine but there is only one more then that will make it completely , i need that all cells that are not formatted as percentage or general , and have value of 0 will have the currency symbol as the rest of the numbers , how we do that ??? i tried to play with your macro around but i have hard time to get it correctly , please can you help me with that ???thank you in advance ,
 
Hi ,

I have not understood your requirement ; suppose a cell has not been formatted as Percentage or General , and has a value of 0 ; the selected symbol may be either a currency symbol or the percentage symbol , since these are all available in the dropdown.

What should happen in these two cases ?

1. Cell value is 0 , and the cell has not already been formatted to Percentage or General , and the formatting symbol selected is Percentage

2. Cell value is 0 , and the cell has not already been formatted to Percentage or General , and the formatting symbol selected is any of the listed currency symbols

Narayan
 
what i mean is
1. Cell value is 0 , and the cell has not already been formatted to Percentage or General , then it should have the formatting of the symbol selected
 
give you an example , I formatted all the umbers in it should be , but when you change currency see what happened to every cell that contain value of 0 , what i need is that those cells will change as the other cells that are more then 0 (currency ways)
 

Attachments

  • FACS-MIKE_1.xls
    465.5 KB · Views: 1
Thank you very very much that it's working wonderful, i must say you are a gentleman , now the only things that we need to finalized is the project ? looking forward to your answer, thank you again
 
You must log in or register to reply here.
Back
Top