Multiple move/delete #N/A with vlookups

[BlakeSkate]

Please Login or Register  to view this content.

This is the current code i', working with, and it somewhat works for the first maco sub, but i'm at least 80% sure that there is a waaay easier way to accomplish what i'm going for:

Okay so i spliced together 4 subs which the first 2 work (sometimes), but the last 2 do not?
My objective is simple, and you can use this as a reference: https://app.box.com/s/28q2ibnryvu60i8sayj0atbiza4m4qym

So here is the plan:
1. Vlookup column A in "Master Supra" to lookup sheet "Data Creation"
2. Anything that comes up #N/A move to "Supra Disco" & Delete from original sheet
3. Delete vlookup column

4. Vlookup Column A in "Data Creation" to lookup sheet "Master Supra"
5. Anything that comes up #N/A copy/paste to "Master Supra" WITH THE APPROPRIATE COLUMNS (highlighted yellow?) (KEEP IN ORIGINAL SHEET)
6. Delete vlookup column

*NOTE* i had not attempted moving the data from Data Creation -> Master Supra with the correct columns
I.E: "Title Generator" goes into "Item_Name", "Price Calc" goes into "Price". I'm not so lost that i can't figure out how to change the destionations of the cells, i just don't know VBA to this extent i've just been splicing VBA from things i find on the web.

Any and all help would be AMAZING

[TMS]

Please upload the sample workbook in the form and I'll look at it.

Regards, TMS

[BlakeSkate]

Please upload the sample workbook in the form and I'll look at it.

Regards, TMS

Thats what the reference link is. unless there is another specific way i should be sharing the worksheet? (sorry if i missed that rule)

[TMS]

You have provided a link to an external host; not everyone will download from elsewhere, me included.


To attach a sample workbook. 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 use the paperclip icon to open the upload window.

View Pic

[BlakeSkate]

FOREXCEL.xlsx

i believe this is it

[TMS]

This just gets the required records from A to B. I'll leave you to sort out any restructuring.

Please Login or Register  to view this content.

[BlakeSkate]

Hey TMS,
that worked really well for getting everything into the disco sheet, and i'm assuming this is just for steps 1-3?
Can i contact you if i have any troubles getting this to work from 4-6?

you've been a huge help thanks!

[TMS]

Yes, that's just steps 1 to 3. If I'm honest, I didn't even notice steps 4 to 6.

It should be pretty much the same as steps 1 to 3 with the worksheets changed. You can use the same Helper column; obviously, the COUNTIF formula needs adjustment. And don't delete the rows after the copy.

I don't think there's any short cut ... you just need to re-work the code.

Good luck with it.


Regards, TMS

[BlakeSkate]

Good luck with it.

Thank you, i'm sure i'll get it

[BlakeSkate]

TMS, i actually got the code to work for the "adding to the Master Supra" step
Kudos to you for having an easy to understand naming convention (wsMS, wsSD, wsDC)

I am having trouble with step 5 in that i need to organize the data. I have a question that has nothing to do with the code you provided: Can i use countif to identify a starting point for simple cut and pastes? I've attached another sheet to illustrate my dilemma. So if you click on column A and CTRL+Down you will notice the gap where the new data is being entered. My approach was initially

Please Login or Register  to view this content.

BUT there are some columns where there are blank cells so "Selection.End(xlDown).Select" is not accurate enough. Is there a way to move this data right below the gap to the appropriate columns while maintaining its row number?

Thanks for the help!

[TMS]

I would be inclined to do something like this:

Please Login or Register  to view this content.

Not really comfortable about establishing the first row but, if that's how your data is structured ...


Regards, TMS

[BlakeSkate]

TMS, if you think there is a more efficient way then by all means. i was just doing what i know how to do (which is not much)

[TMS]

No. It works. I'd probably not leave a gap and save the start row when I copied the data there. Then a) you already know where to start because you just put the data there and b) all you need to do is establish the last row. You only need to do those things once, as in the example, and you can copy and clear as many ranges as you want. I'm not using Cut in case it moves columns across ... prefer two stage Copy and ClearContents.


Regards, TMS

[BlakeSkate]

Thank you so much for your help!

[TMS]

You're welcome.

[BlakeSkate]

Pay no mind to this post i figured it out

[TMS]

The code looked OK, as far as I could see without having the opportunity to test it. What did you do to "fix" it?

Regards, TMS

[BlakeSkate]

What did you do to "fix" it?

Please Login or Register  to view this content.

^ i changed it so it pasted? it works perfectly now with 0 bugs no matter how much the data changes so again THANK YOU

[BlakeSkate]

Also asked TMS for those of you that might encounter an error when there is no new data to add from one worksheet to the other:

Please Login or Register  to view this content.

thanks TMS!

[TMS]

You're welcome.