find, copy, delete, paste

[jwedje]

Hi all,

I'm quite good at working with excel, but have almost no knowledge about Visual Basic except very basic stuff. I would appreciate if somebody could help me with the following problem:

Let's say you have 4 worksheets: 1st is called Sold in Q1 2007 (this sheet is updated each quarter with new data and old data is deleted), 2nd Inventory 1, 3rd Inventory 2 and last Sold units 2007.

What I would like to be able to do is the following:

When I have the data in the first worksheet (let's say 10 rows with 10 serial numbers) I would like the program to look each serial number up in column A of Inventory 1 and if not there in column A of Inventory 2 and if not found at all give an error message. If it is found I would like that row to be deleted from Inventory 1 or Inventory 2 and pasted in to Sold Units 2007 with all the info of that row in there.

When that is done and new info is inputted in the first worksheet which will now be named Sold in Q2 2007, I would like to do the same process but that it starts form the last row from Sold units 2007, so that I have at the end of the year a worksheet with all units sold in 2007.

I tried uploading a small sample excel file, but that didn't work.

Can anyone help me on this?

Kind regards,

JW

[mperrah]

You need to make I think 5 posts before you can attach a file.
Not sure if its 5 but thats a start.
I have been working on a file that does a lot of what you need.
I'm still learning vba, but this may help.
This forum is a great resource.
I'll see if I can post my file...
Mark

[mperrah]

Here is a file I made for inventory management for directv.
Input sheet is where you add receivers- this could ber modified for your selling items
the receiver sheet has the onhand inventory - like your inv 1 and 2
the recon sheet is a log of all the transactions.
the invoice sheet tracks each item leaving inventory.

I think with a little tweaking, this file could fit to what you are looking for.

hope this helps.
I got all the code for this project here. at vbax

Mark

[jwedje]

Thnx Marc,

At the moment I cannot view the attachment. Would it otherwise be possible to send the attachment by mail?

[jwedje]

After previous post I can now see your attachment. Thnx and I will let you now if it works.

[mperrah]

I thought it was 5 posts, anyway
glad to help.
Mark

[jwedje]

Just to let you know. I have not yet been able to solve it fully, but I have been able to use parts of your code and got it sort of working.
thnx

[mperrah]

glad to help,
what part is giving you trouble?

[jwedje]

Marc,

Attached is a sample file, but now apparently nothing works anymore.

I left a small explanation in the first sheet of what I need.

I guess I need to follow a course VB.

[mperrah]

I can try to help.
I noticed your named range was showing a Ref error.
I added in dynamic names for all your ranges.
This way if any item gets added or removed they should still work.
Also I found a file that helped with sorting,
I'll attach that too.
I will try to get more help as time permits.
I just finished a huge project and should have some time early this week.
Mark

[jwedje]

Marc,

As B6 in Sold sheet was blank in the beginning it off course didn't do anything, so I changed that to B2 and now that part is working again. Now, the deleted info in Inv 1 should somehow be pasted into the Sold Units sheet. Have any ideas?

Kind regards,

JW

[jwedje]

Update:

1. These serial numbers need to be removed from inv1 & inv2 and the empty rows need to be moved up. Solved for Inv, except how do I get the program also to look in Inv2 sheet?
2. When removed in inv1 & inv2, the whole row with info needs to be pasted in SoldUnits sheet. Still an issue.3. When all this is done the serial numbers in column B of Sold sheet need to be removed. Solved4. If one of the units in column B of Sold sheet are not found it needs to give an error message for those serial numbers that are not found. I have no idea, how to do this.5. When I do this a second time and give input into the sold sheet, it should do the same process and start on the last row of the SoldUnits sheet. ???

[Aussiebear]

Is it possible to concatenate both inv sheets into one? If so then, then I'd then use vlookup to find if the serial numbers exist on the combined inv sheet and set a trigger value. For those not found set up a trigger value on the Sold sheet. Trigger value could be an additional column at right of existing data formatted to marlett and use a "tick" if foound and a "cross" if not found.

A second action would be to then review the concatenated inv sheet and copy and paste special, the data rows with the "tick" value to the Sold Units sheet, then delete the row just copied. Loop this action until no more trigger values are found.

and as a final action, set up some code to review the initial sheet and delete any row that does not have a trigger value ("Cross") marked against it. all rows left will then be those rows you need with the "error" message.

By deleting rows the sheet closes up to complete one of your other requests

[mperrah]

Check this file out.
If you click in the target row (column "A")
a check mark is added.
you can use the find and offset.select to move the focus
and fire a sub that way.

as far as deleting,
on match find and copy, the next line could be something like.

find match.offset(0,3). select
with selection.delete
end with

I can try to code it out if this sounds like what youre looking for...

this is code from the invmngr I pasted, i think is close...

[VBA]
Dim rng As Range
Dim rng2 As Range
Dim cell As Range
Dim cell2 As Range
Dim sh_start As Worksheet
Dim sh_dest As Worksheet
Dim i As Long
Dim rcvr As Range
Dim r As Double
Dim lrowRcvr As Long

Set sh_start = Worksheets("Invoice") ' this could be sold
Set sh_dest = Worksheets("Receivers") ' trhis could be ' Inv1 and alter for Inv2
'If nothing present at invoice sheet on first line
If sh_start.Range("G6") = vbNullString Then Exit Sub ' make this a named range on sold
'search on receiverno (unique ?)
Set rng = sh_start.Range("G6:G" & _
sh_start.Range("G" & Rows.Count).End(xlUp).Row)
Set rng2 = sh_dest.Range("A2:A" & _
sh_dest.Range("A" & Rows.Count).End(xlUp).Row)
'loop for the invoice
For Each cell In rng
'loop for the receiver
For Each cell2 In rng2
If cell.Text = cell2.Text Then
'Remove name of receiver
' sh_dest.Range("A" & cell2.Row).ClearContents
sh_dest.Range("A" & cell2.Row).Resize(1, 4).ClearContents
Exit For
End If
Next cell2
Next cell[/VBA]


I can tweak it more but it looks in the source (sold) and one at a time compare to Inv1, if match found, run some code,
if not take the next item on sold and star compare...

Hope this helps
Mark