Code working as change event, but not as a macro.

Im am currently using the following code on a Worksheet_Change, but I need to change it to a Module I can run with a macro button because its affecting other change event code I have. However although it worked fine as change event code, it is not working when I run the code as a macro. I get run time error 424 'Object Required' on Line 1. Im assuming I need to tell it the sheet it needs to work on. How would I do this? The sheet in questions is called ReturnData.
Thank you,

Please Login or Register  to view this content.

Change the "Target" as "Activecell"

But i would like it to apply it do every record (from 6 on) on the ReturnData sheet when ran, not just one cell at a time. If the row already contains data in the target rows H and J then it should ignore them.

I do not know why do you need to use both a loop and find function. One would be suffice.
Untested.
Instead of the target I have changed the code to loop through column E.

Please Login or Register  to view this content.

Thank you for your time. There seems to be an issue with that code. When I ran it the message box popped up, and every time I clicked OK it kept popping up again (I had to force quite excel).
I have attached a copy of my workbook. On the ReturnData page if you select the Start Scan button it will take you to the bottom of the page. The code is ran by pressing the Autofill Unit Button. On this workbook since record 417 shows serial 59595 (G column) as receive (E column) to the 'A Cam -1st' unit (H column), on line 418 that same barcode is marked as return, so pressing the Autofill button should enter 'A Cam - 1st' unit into the H column to.
At the same time the autofill button should also copy H421 into cell J425 (sine the item get relocated).
Thanks,
James

I apologize, my VB code experience is limited, I appreciate your patience.
This is a little complicated to explain, but I will do my best.
The code should search for records containing data in G, with the E column equal to (Return, Relocate, Lost, or Damaged), while also missing data in either H, or I, (or also J if E = Relocate). If it can not find any then it displays the message "There are no records requiring autofill data".
If it does find a record missing data in those cells then it should do the following:
If E is equal to either Return, Lost or Damaged then it should find the first row above with matching data in G (there could be multiple matches in G, but it should only reference the first match above the record in question) and copy the data in H and I in that record to the corresponding record.
Relocate should do the same, however in this case the data in H should be copied to J (I still gets copied to I).

Thanks,
James

James,
When you say search for column G, do you mean if cell in column G is blank? What are you searching in column G for?

My guess is this you want to test if a cell in column G is blank and while column E equals to the 4 conditions?
On the attached, there is no blank cell in column G, while there are some blanks cells in H423 and H424,I424

Sorry, if the cell in G is blank then it should ignore that row (since every record has to contain data in G). If G contains data, and E is equal to one of those four conditions and is also missing data in either or all of those specific cells then it should look for a match above in column G to copy data from (in those specific cells, depending on what E is).

Try this amended code

Please Login or Register  to view this content.

Awesome, thank you millz, that works nicely!
However its just duplicating the data in the H column right now, I would like it to also duplicate the date in the I column. The I column duplication should have the same rules as for H column (and again based on the E value), only its a little simpler since duplication is always I to I.

You mean adding this line?

Please Login or Register  to view this content.

Or this?

Please Login or Register  to view this content.

Just add below:

Please Login or Register  to view this content.

I used the first line (as I want to copy I to I), and i put it both after

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

However, if the I column is the only data that needs data copying (because the H column data has already been input manually) then it doesn't work. It only works if both H (and or J) and I are blank. Can it be changed to work even when I is the only cell thats blank?

Thanks.

Change these conditions:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thank you millz, you rock!

Millz I just discovered a minor issue with the code. I have some conditional formatting which highlights specific cells that are empty reminding the user they need to enter data. Half of the time the data is enter using the code you made for me (by referencing data the user already entered). However when this happens it seems to be also copying the conditional formatting rules for that cell (and creating new rules every time). How can I prevent this from happening?

Sorry, just came across another issue to. Sometimes I have entries for items that dont contain barcodes, so the user types none into G. Therefore when the code searches for the last record above by matching the data in G, if the G cell equals none then it should also find a match for the data in F as wells as G when searching for the matching record above (other wise it just finds the first record above that also happens to not have a barcode, which may not be correct).
Thanks,
James

That's because your code is indeed doing copy and paste, you can try changing to using value = value. And if I understood your 'other issue' correctly, try this.

Please Login or Register  to view this content.

That fixed the issue that was affecting the conditional formatting, but it doesn't seem to be finding the records where G equals none.
I also added in a couple of lines, as that last code was not copying the data in I in each case. However I have noticed that if I already contains data it will overwrite it. Just like H and J if there is already data in I then it should not overwrite it (it should only enter data if the cell is blank).
I have attached my workbook to make it easier.
The worksheet is ReturnData. The button Autofill Unit/PO at the top will run the code (which is in module AutoFillData).
With the G = none issue, in the current state on the workbook it should match record 430 to record 428, and duplicate H and I from 428 to 430 (it should ignore record 429 since column F does not match).

Try this now:

Please Login or Register  to view this content.

Thank you millz, your a genius!