Hi All,
I recently started to learn VBA coding. I am far from having a advanced skill set or knowledge on it. But I would like to know if my end goal is possible. I have a HUGE spreadsheet with data in Columns, Table Format. This file gets updated once a month and the update process is manual and very exhausting. Now the process i go trough every time is the Following:
1. Run a report for created dates for "claims" from current date and 4 months back.
2. I copy and paste all the relevant columns to my data from the new report (temporary file) to the main file (Huge fail).
I don't have any problem with the first two steps I basically copy and past about 4-6 columns of new and repeated data.
3. I sort by a "claim number" from smallest (older dates) to largest (most recent dates), this number is unique for each "claim".
On this step I use Conditional Formatting to Highlight all the repeated claim numbers.
4. Verify all claim numbers and update the "Status" (Closed,Open, etc...), update any of the columns that have changed for each repeated claim number.
The last step is the one I am interested in automating.
What I have been using to make this step less overwhelming is to add 2 extra columns and the first one is a "IF, OR" Statment where it looks at a cell and looks the the value on top and under it, if they are the same it ives me TRUE if not then FALSE. This column helps me on filtering for all the repeated claim numbers. The repeated claim number is always under the "real or true claim number". I say repeated because the row in which the repeat. I also compare the top and bottoms so this way when I filter it leaves the top and bottom numbers. Then the second column I add uses a "IF, AND" statement where it looks at all the columns I manually update and if the selected cells are the same as the one under it(all of them) it gives me "DELETE" if not then "REVISE". Once I add these columns I Copy and Special Paste Values on them and then sort for the Delete or Revise Column and delete the "DELETE" Rows. Once ALL of this is done then I look at every single repeated claim number and look at the columns to the right and left of it if they have changed I copy them and paste from the "Temporary" Row to the "PERMANENT or MAIN" Row, then I proceed to delete the temporary row. I know how to do this on the columns and what I would like VBA to do is.
Given a starting cell and a ending cell which in terms is a range from A"starting cell":A"Ending Cell". Then Look at every claim number and compare it to the one below it if they are the same then look the columns that need update if they are different then have it copy the below value and paste it on the top value and then delete the temporary row which is the row under the cell he is looking at. Then it would move to the next claim number and repeat if the claim number is the same as the one below.
From what I know I don't know how to make VBA ask for the starting and ending points. I think with a simple IF, THEN statement it could be solved, but with my super limited knowledge I still have not been able to grasp the coding. But the question is if VBA can do this. Look at a cell compare it to the one below and if they are the same look at the cells that i need to update usually in the left and right of the main cell, compare them to the cells under it copy and paste if they are different and then delete them and repeated with the rest. Also if the claim numbers are the same and nothing has changed then have it also delete the temporary row.
On the flip side I think it could be easier if I just tell VBA to look at claim number compare it to the one below and if they are the same then just copy and past all the cells I update regardless if they are different or not and then delete the temporary row. This way I wont have to nest codes over codes. I would be the more simple version of the program. Like I said I am learning and I don't know how to do it yet. But is it possible?
Thank you all in advance for reading this extensive explanation, and for you very valuable time and feedback. I started learning some of the basics on Chandoo and it gave me ideas on how to work with it, I have had some previous college experience of coding with matlab but I just know concepts not how to code. If you could also recommend books or other links that have tutorials I appreciate it too.
Bookmarks