vba to change the length of cell then converting the file into text

[vinod9111]

Hi All,

I have to report account level data to regulator, they have described certain norms on the data submission. The data has to be in 25 fields , each field has its length and it should be in text format.

I need a macro firstly to check the length of each field as given below in case of error it should highlight the cell number where it is not inputted as per the requirement. There are certain fields which needs to be kept blank, which is mentioned in the remarks column. The data size is huge and to do this task manually takes lot of time.

Once the length of data is okay it has to be converted in text file with the length give below, against the blank field there should be number of space as given in the length, data format has to be text except for two fields as given in data format.

Hope i make sense, any help will be appreciated

[TABLE="width: 480"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Fields[/TD]
[TD]Length[/TD]
[TD]Remarks[/TD]
[TD]Data format[/TD]
[/TR]
[TR]
[TD]Job_Code[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]Month_[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]Year_[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]FillerI[/TD]
[TD]1[/TD]
[TD]Blank[/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]BSR_CODE_PART_I[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]FillerII[/TD]
[TD]1[/TD]
[TD]Blank[/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]pag_no[/TD]
[TD]4[/TD]
[TD]Blank[/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]sr_no[/TD]
[TD]4[/TD]
[TD]Blank[/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]FCL[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]dist code[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]population grp_Code[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]type of acct[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]org code[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]occupation[/TD]
[TD]5[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]nature of borrw acct[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]asset class[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]Secured/Unsecured Loan code[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]Fixed/Floating interest Flag[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]BANK RATE1[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]AMTFIN_roundoff[/TD]
[TD]10[/TD]
[TD] [/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]POS_roundoff[/TD]
[TD]10[/TD]
[TD] [/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]Lot_no[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]Colan[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]Unique ID[/TD]
[TD]20[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]Semi colan[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]Text[/TD]
[/TR]
</tbody>[/TABLE]

regards
Vinod

 

[vinod9111]

Hi Guys,

Any help would be appreciated.

thanks
Vinod