change format of part of column
Hi, I'm trying to make my code change the format of 9 columns. they all have the same amount of rows but these may differ each time the macro runs. I tried the following code with an array, but it doesnt work at all. Can you please help me to adjust it 'till it works? and maybe tell me what I did wrong?
Thanks in advance.
Also, this is all the code I use for this function so nothing else in "option explicit"
Please Login or Register to view this content.
Last edited by stielo; 12-01-2015 at 11:03 AM. Reason: solved
How does it not work?
How are you actually running the code?
PS I would have expected to see code like this in a Sub not a Function.
If posting code please use code tags, see here.
non-confidencial.xlsm
attached is a edited version of the workbook I have at the moment, clicking the button will make another excel sheet with all the data needed.
you just need to fill in the column header (INV 800 and so on) and a discount percentage, the other box is just for the name of the file.
The code itself looks a bit ugly (in my opinion), but everything works just fine, except for the formattingI'm fairly new to coding, so any help on improving my code is greatly appreciatedPlease Login or Register to view this content.
How does the formatting not work?
Do you get errors?
Is the formatting not applied properly, or applied to the wrong range(s)?
There is one obvious mistake, the array should be declared as Range and dimensioned from 1 to 9.
Please Login or Register to view this content.
I get an error about " object needed in thepart, I will try changing it to range now and will post back if anything changedPlease Login or Register to view this content.
Also, did you get the attachment in my previous reply?
Edit:
I get an error on the following line for subject out of range (err 9) after changing
Please Login or Register to view this content.
Last edited by stielo; 12-01-2015 at 07:35 AM.
How exactly did you change the code?
If I use the declaration I posted in post #4 the code runs without error.
sorry, just rechecked, works fine, I forgot to add the "(1 to 9)" as I must have missed it when changing the declaration.
Just for being a noob, i want to ask you if you have an idea how to optimize the code so it works more smoothly and faster (now my screen just freezes when I run the code) I don't even get the progress bar. (this is needed as some of the people that work with this file are afraid when nothing happens on their screen and nothing responds when clicking.) if you can give me some pointers I'll be happy enough, cause i prefer learning from my mistakes and be a better vba programmer.
Anyway, thanks for your help, will def give you the well deserved rep
I didn't take a very close look at the code but one thing I did notice was that you had numerous loops that were essentially going through the same range.
It might be possible to adjust the code so you only have one loop.
If that can be done it should significantly cut down run time.
There are probably a few other things you could do that will help, for example turning off screen updating.
Please Login or Register to view this content.
will try, and if achieved I will keep you posted if interested (unless i need to start another thread if I have another problem with the loops)
I also tried using a nested loop for both the column and row numbers but this didn't work well, hence the multiple loops (as it will always be 10 columns)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks