Find discrepancies and copy and paste onto an error log

[stitchoz]

Hi,

I would like to find some assistance on creating a macro that performs multiple tasks, generally I just record what I'm doing but for this project it changes weekly.

Here is the variables I'm working with.

Row F=Pay codes
Row G= Hours
Row H=Rate
Row I=Gross

If any Travel Payment code have 0 hours or 0 gross or a rate that does not equal 400 then it is an error.
If any RET codes do not have 22 under Rate then it is an error
If any REG codes have 1 hour then it is an error
If any OVT codes have 1 hour that fall on a weekend then it is an error
If any codes have "no work" then it is an error

If any errors are generated from these variables, I would like to copy and paste onto a new sheet.

Is it possible to design a macro to this this?

I tried the below code as a test for the first variable but got a debug error

Please Login or Register  to view this content.

[Marc L]

Hi !

For your 'debug error" check in your code the value of the variable i …

[stitchoz]

I set i as cell.row but now get Object required error.

[stitchoz]

Forgive me, I don't use macros often as I'm not a programmer. Mostly my macros are simple recordings I made with recording, unfortunately I do not know how to create my own codes as you can obviously see :-\. I do this project once a week at work and takes about 2 hours manually, so if I can just cut that time down a bit any suggestions are appreciated.

[xladept]

For one thing this code will yield nothing:

Please Login or Register  to view this content.

it must be:

Please Login or Register  to view this content.

But could you:

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[stitchoz]

I have attached samples of what I'm trying to do, forgive me on my amateur macros built in. I had to do the results manually as I don't have a working macro to finish what I'm trying to do.

[xladept]

Your after input sheet has a column for rate while your before sheet does not - which is the valid input?? And is the rate criterion really exactly 22?

[stitchoz]

Yes, for the Retainer code it must always be a 22 rate, if it is anything other than 22 it is wrong and needs to be on the error log sheet.

The original does not have rate column because that is not on the export data so I add the rate column and formula via macro.

I got everything up until the point where I begin searching for errors, the last code is what runs in order right up until the error search.

I'm sure there is a better way but I'm ignorant on creating VB and rely on recording.

Please Login or Register  to view this content.

Your after input sheet has a column for rate while your before sheet does not - which is the valid input?? And is the rate criterion really exactly 22?

[xladept]

Using the before sheet, this is what I've got so far:

Please Login or Register  to view this content.

[stitchoz]

Seems it is going in the right direction but tested it out and it is coming back with 94 line errors when it should only be 13 line errors.

[stitchoz]

Also found that it is bringing back different results than the original for example on supervisor I and this occurs on others

original:

Last Name, First Name2 2 Supervisor I 9/23/2019 Retainer 8 176 22
Last Name, First Name2 2 Supervisor I 9/24/2019 Retainer 8 176 22


Log:
Last Name, First Name2 2 Supervisor I 9/23/2019 Retainer 8 0 75.2
Last Name, First Name2 2 Supervisor I 9/24/2019 Retainer 8 75.2 44

[xladept]

But what about my questions? I posted the program although it's in progress. All the lines are just from the "Catch 22"!

[stitchoz]

My apologies for not understanding.

The before sample does not contain a rate so it must be put in by calculating column H/Column G, the rate result would be Column I with =sum(H/G) formula. Then search for any codes in Column F with the "retainer" code and if Column I does not equal 22 in that row with "retainer" then it is an error. I hope this is a better explanation.

But what about my questions? I posted the program although it's in progress. All the lines are just from the "Catch 22"!

[stitchoz]

Minor edit:
Column F Pay Code
Column G Hours
Column H Gross
Column I Rate (should be)

But what about my questions? I posted the program although it's in progress. All the lines are just from the "Catch 22"!

[xladept]

Well, I can't reconcile that "22" - try running this on the before sheet, I've cleaned it up somewhat - let me know what needs done

Please Login or Register  to view this content.

[stitchoz]

Xladept, below is a working code I was given by a member in another forum group. If you would like to provide insight or use let me know. I figure I'd share this on here too.

Please Login or Register  to view this content.

[xladept]

Thanks anyway stitchoz

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Winon]

Hello stitchoz,

You have been naughty with Marc L and xladept, in that you state;

below is a working code I was given by a member in another forum group.

It violates Rule 03 of this Forum Rules.

Personally, I feel that you owe them both an apology.

Administrative Note:

You brought it to our attention that you have posted the same query on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. It is mandatory that you provide a link to any of the other Forum/s.)

Thank you.

[xladept]

Thanks for the rep!

[stitchoz]

You're welcome xladept! Thank you for the insight you gave me on the codes and thank you Marc L, definitely a different take that makes me want to learn more. I will be looking more into them on my own spare time to learn more.

I apologize if you took the different code as an insult in any way, it was not my intent. I just understood that version more clearly due to my lack of knowledge in coding.

Winon: Unfortunately I am unable to share as the communication was done in private messaging, not completed on the forums. I shared the code from the private messages onto the forum. I had no intent from this to result in a violation, my apologies. I have read the details you linked and I will happily comply in the future, thank you for sharing that information.

[Winon]

Hello stitchoz,

Thank you for your follow up and clarification.

Regards

[xladept]

Well - I just put another tweak in the code since my regard was renewed - see the red:

Please Login or Register  to view this content.