tcorbs
05-18-2011, 07:58 AM
So here's what I want to do. I am trying to create a macro that would compile all the claim numbers (strings) and check amounts from the various source worksheets into a list in the "ClaimList" tab. However, I want to add the check amounts for duplicate claim numbers (like a pivot table).
Part of the issue is that the "claim #" column consists of a claim number, then anywhere from 1-4 empty rows, then another claim number, and so on to the end. And I can't delete out empty rows (they have impt. info in other columns). Fortunately, there are only check amounts in the rows where there is a claim number. Furthermore, the "claim #" column varies from worksheet to worksheet, as does the column for check amount. However, the columns are both always titled, and the column titles are always in row 4.
What is the best way to do this? I set out to do it on my own, but I don't know enough VBA to do it in a reasonable amount of time. I'd also probably end up going around my ass to get to my elbow, or however that expression goes. Anyway, I have the tabs set up in excel as follows:
'ClaimList', 'Source_1', 'Source_2', 'Source_3', etc.
So I think my algorithm should be:
-Go to 'Source 1' tab
----Find claim number column
----Find check amount column
----Go down to first claim number (skipping blank rows)
----Check named range (check_rng) to see if this is a new claim (which it will be if this is the first claim)
----Copy to master list in some cell defined as new_claim_cell
----Copy corresponding check amount to master list next to new_claim_cell
----Add the above cell to check_rng where I can check for repeats
----Move new_claim_cell down one
----Go down to second claim number (skipping blank rows)
----Look in check_rng to see if it is same as first claim
----If so, simply add check amount to corresponding cell in master list
--------then go on to next claim number
----If not,
--------append to master list in new_claim_cell
--------Append corresponding check amount
--------move new_claim_cell down one
--------add above number to check_rng
----Go to 3rd claim number and so on through 'Source 1' (you will know you reach the end if there are 10 consecutive blank rows)
-Then go to Source 2 tab, and do the exact same thing, keeping the first master list intact, but appending any new claims from Source 2, and adding check amounts to any claims that were already listed.
-Finally it would be nice to have on the master list a most recent change column which displayed only the check amounts of the last source (mainly so you can see the amount of new payments that were made to old claims)
Unfortunately, I don't have Excel jeanie at work to show you exactly what I'm working with. Let me know if you need more specifics though. Please help! Thanks in advance.
Part of the issue is that the "claim #" column consists of a claim number, then anywhere from 1-4 empty rows, then another claim number, and so on to the end. And I can't delete out empty rows (they have impt. info in other columns). Fortunately, there are only check amounts in the rows where there is a claim number. Furthermore, the "claim #" column varies from worksheet to worksheet, as does the column for check amount. However, the columns are both always titled, and the column titles are always in row 4.
What is the best way to do this? I set out to do it on my own, but I don't know enough VBA to do it in a reasonable amount of time. I'd also probably end up going around my ass to get to my elbow, or however that expression goes. Anyway, I have the tabs set up in excel as follows:
'ClaimList', 'Source_1', 'Source_2', 'Source_3', etc.
So I think my algorithm should be:
-Go to 'Source 1' tab
----Find claim number column
----Find check amount column
----Go down to first claim number (skipping blank rows)
----Check named range (check_rng) to see if this is a new claim (which it will be if this is the first claim)
----Copy to master list in some cell defined as new_claim_cell
----Copy corresponding check amount to master list next to new_claim_cell
----Add the above cell to check_rng where I can check for repeats
----Move new_claim_cell down one
----Go down to second claim number (skipping blank rows)
----Look in check_rng to see if it is same as first claim
----If so, simply add check amount to corresponding cell in master list
--------then go on to next claim number
----If not,
--------append to master list in new_claim_cell
--------Append corresponding check amount
--------move new_claim_cell down one
--------add above number to check_rng
----Go to 3rd claim number and so on through 'Source 1' (you will know you reach the end if there are 10 consecutive blank rows)
-Then go to Source 2 tab, and do the exact same thing, keeping the first master list intact, but appending any new claims from Source 2, and adding check amounts to any claims that were already listed.
-Finally it would be nice to have on the master list a most recent change column which displayed only the check amounts of the last source (mainly so you can see the amount of new payments that were made to old claims)
Unfortunately, I don't have Excel jeanie at work to show you exactly what I'm working with. Let me know if you need more specifics though. Please help! Thanks in advance.