I am writing the following macro that will add leading zeros to postal codes outside the US WHICH IN FACT DO use leading zeros. The reason for this is that my csv file drops leading zeros, so i am hard coding it
I am getting a error on the ActiveCell.FormulaR1C1 = "=IF(L2="USA",RIGHT("00000"&L2,5),L9)"
line PLEASE ASSIST
Please Login or Register to view this content.
Double up the quotes:
Please Login or Register to view this content.
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
Suggest you post a sample workbook. The code, I am sure could be a lot shorter than that.
Not sure exactly where the formula is meant to be going but, as an example:
Please Login or Register to view this content.
Regards, TMS
i have a raw file that comes in with postal codes that are missing numbers. basically for all the USA codes in column K2 all the way down that has a postal code less than 5 digits i would like to add "0" in place. so for code 345 it would be 00324. and so on.
Column K1 HAS THE POSTAL CODE HEADER and K2 down is where the data is entered. Column L1 is country code an L2 below could be USA,BRA,JAM etc. Thus the below code which is giving a error on
'ActiveCell.FormulaR1C1 = "IF(L2=""USA"",RIGHT(""00000""& K2 ,5)""K2"")"
so the code should copy the formula from K2 all way in the record
Adjust the code provided in post #3 to refer to the appropriate cells.
Note that the formula is NOT in R1C1 format,, it is in A1 format so you need to use .Formula, not .FormulaR1C1
Please Login or Register to view this content.
Change the x to refer to the column where you want the formula.
Regards, TMS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks