Creating IF statement

Hi Robert, my fellow Aussie!!

I put in your code (in the way I understand I was supposed to... not very experienced here!) but it still throws up an "object required" error on the CurrClaims.EntireRow.Select line... Here's what I did - and by the way, I put the "end if" just before "'delete all blank rows from "current claims" tab" seeing as though if there are no claims, it only needs to do the actions from that line down... does that make sense?:

Please Login or Register  to view this content.

Because I am not really awesome with this stuff - here's my whole code... maybe this will help!

Please Login or Register  to view this content.

Thanks Robert - hope I did this right, as unfortunately it's still throwing the object required error at the same line..

Please Login or Register  to view this content.

Okay - will just have to see if I am allowed given it's an internal document. I'll see if I can provide it if I blank out any identifying information... just give me a moment and I'll find out!

Alrighty, here it is, all blanked out but hopefully you will get the drift...

and here's what it does when there are actually claims to move:

I think that would be because (I am presuming) the second time the macro is run, there is no data to select in "new claims". This won't ever be an issue because there will always be data in "new claims" to import into "current claims"; there just won't always be data to select from "current claims" to move into "old claims".

That seems to be the issue, when there's claims to move from current to old, it works okay but as soon as there is nothing and no range to select, it gets upset.

I presume this is what you meant, but it did the same thing and threw an error on the same line (object required error)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks Rob, I wish I could say it worked! I can see what you've done but unfortunately now it moves all the claims to "old claims" when it should only move the claims which are on the current claims but not in the new claims when they're imported.

So essentially the sheet does this when the macro is run:
- pastes all new claims into current claims
- looks for duplicates and with those gets the data from the old current (duplicated) claims in columns from AG- AH and assigns them to the new duplicates and does the same fr column H but applies that to the respective claim in column I
- it then clears the old duplicates
- it also pastes any new, non duplicated claims into current claims

I'm then left with two ranges - new duplicates and new claims down the bottom and any "old claims" up the top. The old claims are any left over claims that were not on the new claims list. The new claims are any duplicates plus any claims that were not already in current claims.

- it then removes any blank lines from the old claims range and puts all that data into the old claims tab.

However when there are no old claims that range does not exist and so the macro has an error (from my understanding).

I thought if I could tell it that if the old current claims range was not there then to just sort by dwc and so on and a forth but if it was there then to execute the process as per the macro. But my if statement attempts never worked.

I really wish I could solve this

Were you able to run that macro? I've put your code in there and the first time it did something odd - instead of adding the dates when the data went into "old claims" it added the dates in the new claims. I don't know why this was, so I thought I'd run it again into a new sheet. I stepped into the macro and note that it appears not to be selecting the old claims sheet now.

Sheets("Old Claims").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
On Error Resume Next

I'm suspecting it has something to do with the error code??

Actually ignore what I said, it's not supposed to select old claims, so what you did was great however if there is no range to select then it should skip to this bit (because it won't need to execute any processes in the "old claims" tab as it won't be adding any old claims...

Please Login or Register  to view this content.

Okay I have changed it slightly and now it seems to run... touch wood. Did you want to have a look and see what you think?

Please Login or Register  to view this content.

And yes I am not sure about this part either, I didn't design the code, I've just modified it to accomodate tweaks I've made to the sheet.

Please Login or Register  to view this content.

would you suggest something different?


Thank you so much for your help, I appreciate it a lot!

fantastic thank you Robert. I guess I should use that type of code wherever it's trying to select entire rows?

Thank you so much, you've been such a champ! I am really glad we resolved it, I was starting to get grey hair!