Hi,
I need to format individual columns in a macro, however once I stop recording and test run it the entire sheet ends up in whatever the last column I formatted was
I need the below
A:A = number (no decimal places)
B:B = number (no decimal places)
D:D = number (no decimal places)
F:F = number (no decimal places)
G:I = currency
N:N = number (no decimal places)
O:O = short date
T:T = number (no decimal places)
U:U = accounting
Thanks,
Jess
Please post your code.
Regards, TMS
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
This is the very last portion of the macro, no formatting before this. I have also tried moving the formatting sequence at the beginning and during the macro
Please Login or Register to view this content.
Hi Jess
try this code. I think it'll do what your code appears to do
Please Login or Register to view this content.
Somewhat belatedly, a few variations on a theme, starting with a simple tidy up of the original code.
Please Login or Register to view this content.
Regards, TMS
@Philb1: in your code, you define and set the Ws variable, but then you refer to the Activesheet and qualify all the ranges with Ws.
I referred to activesheet in find, I didn't realise I'd done until you pointed it out, but it doesn't matter as it'll still work. I went activesheet because I don't know what sheet it's supposed to be & presumed the active one was good enough. I always fully qualify my ranges & the reason is I've demolished many a wrong sheet by not doing so lol. It's over the top I know.
Force excel to do what you want it to do otherwise it's like letting a kid loose in a candy store
@Philb1: yep, no problem with the code working. Just highlighting the fact that you'd defined a variable for the Activesheet but then referred to the Activesheet rather than using the variable.
And there's little point using "With Ws ... End With" if you are going to qualify all the ranges with Ws. As in:
Please Login or Register to view this content.
Incidentally, in this instance, rather than use:you can get away withPlease Login or Register to view this content.Easier to understand in this context although not so good if you're cycling through columns and wish/need to use a column number.Please Login or Register to view this content.
Not trying to be picky, though I probably am, just making you aware for future reference.
Regards, TMS
Hi TMS.. Thanks for your interest, I'm always willing to learn
I use numbers with my ranges because they're easy to manipulate, the bits in my code that had letters were copied out of the original, I was to lazy to change itAs far as the With Ws, it's me going over the top with qualification. As I said, I've demolished all my formatting etc by not qualifying properly, so now I over qualify to be on the safe side
Cheers
Phil
Fair enough.
OK, but there's no point using With ... End With if you then fully qualify the range. It's meant a) to be a short-cut and b) to be more efficient because, as I understand it, it creates an internal variable that the .Range, .Cells, .Rows and .Columns refer to.As far as the With Ws, it's me going over the top with qualification.
But I do understand your concerns.
Regards, TMS
@Philb1: thanks for the rep
@jhucker: If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
Neither of these worked for me
It just asks me to debug
Looks like this is an Auckland club here.
Post your workbook if they give an error.
The problem is there is some confidential info that gets typed so I can't just post the workbook
We are just talking about formatting, so you should be able to delete anything that is vaguely sensitive.
Just make sure it still errors before posting.
Sorry Jess, not sure what you expect us to do about that. I tested my code variations and I'm pretty sure they work. I'm guessing Phil tested his code too. But, with no workbook, no worksheet, no data, only partial code, no error message and not even the line of code in need of debugging ... Maybe the worksheet is protected? Maybe you don't have a Sheet1?
doesn't even narrow it down to a specific module. Mine or Phil's or both? Same error? Which would be ...?Neither of these worked for me
It's broke just don't hack it
Oh, and it's a week since we responded to you ... I've slept since then. Once or twice.
Yes my code was tested & was working. As everyone else has said, post a sample workbook that shows the error. Change the data to gobbledegook if it's sensitive
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks