Hi saravanan1981,
Try the attached file which does the following per your instructions:
Processing Algorithm:
a. Sheet Offline (Primary) and Sheet UTR (Secondary).
b. Delete Columns G thru Last Column
c. Find Last Row in Column 'A'
d. Parse Data I nColumn 'D"
e. Save the Max number of items parsed
f. Put data in every other column starting at 'G'.
g. Lookup Data in Sheet UTR and fill in the AMT columns.
h. Count the total number of UTR values in each row.
i. Create a formula for Sum of Each AMT column.
j. Create Headers in Row 1 (Calibri, 11, Bold), ColorIndex = 42 (Blue).
k. Format data (Calibri 11), borders around each cell. Data Cells ColorIndex = 43 (Green), Last 2 columns ColorIndex = 6 (Yellow)
Complete Code follows in Ordinary Code Module ModProcessUTR:
It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:
This option requires all variables to be declared and will give a compiler error for undeclared variables.
To enable Macros and to Run Macros see the following:
http://office.microsoft.com/en-us/ex...010031071.aspx
http://office.microsoft.com/en-us/ex...010014113.aspx
If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.
To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.
Lewis
Bookmarks