Find the first occurrence of a cell and if found do the following 4 things......

[andymalan]

Greetings all you special people, I am aware that this question may have been answered in some way before, but not entirely to my particular challenge.
I have 2 worksheets in a workbook named "Cabinets" and "Hardware". Thank you so very much, In Advance. I need to do the following....

1. Copy from Sheet “Cabinets” AK2:AL1000 and Paste to Sheets “Hardware” A9 after removing duplicates.

2. Find each occurrence of A9 Sheets “Hardware” in the “Cabinets” Column AK and when found, sum the qty and insert the total into Sheets “Hardware”C9

Find each occurrence of A10 Sheets “Hardware” in the “Cabinets” Column AK and when found, sum the qty and insert the total into Sheets “Hardware”C10

Do this until there is no data in Col A

3. Find the first occurrence of A9 Sheets “Hardware” in the “Cabinets” Column AK and when found, copy qty in Col AM and insert into Sheets “Hardware”D9. (Cost)

4. Multiply D9 with C9 and insert the answer in F9

Multiply D10 with C10 and insert the answer in F10

Multiply D11 with C11 and insert the answer in F11

Do this until there is no data in Col A

This is what I have cobbled together so far,
Application.Goto Reference:="BuyOuts"

ActiveWorkbook.Worksheets("Cabinets").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Cabinets").Sort.SortFields.Add2 Key:=Range( _

"AK2:AK1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

xlSortNormal

With ActiveWorkbook.Worksheets("Cabinets").Sort

.SetRange Range("AK1:BB1000")

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Application.Goto Reference:="Code_Item"

Selection.Copy

Sheets("Hardware").Select

Range("A8").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

ActiveSheet.Range("$A$8:$B$1007").RemoveDuplicates Columns:=Array(1, 2), _

Header:=xlYes

Sheets("Cabinets").Select

Range("AK2:AN2").Select

Selection.Copy

Sheets("Hardware").Select

Range("A9").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

End If

End Sub

 

[Akuini]

Could you post the sample by using XL2BB? so we don't need to retype them.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[bebo021999]

Option Explicit
Sub test()
Dim lr&, qty&, cell As Range, arr()
Dim dic As Object
Set dic = CreateObject("Scripting.dictionary")
With Worksheets("Cabinets")
    lr = .Cells(Rows.Count, "AK").End(xlUp).Row
    ReDim arr(1 To lr, 1 To 5)
    For Each cell In .Range("AK2:AK" & lr)
        If Not dic.exists(cell.Value) Then  ' read unique code into dictionary
            qty = cell.Offset(0, 2).Value
            dic.Add cell.Value, qty ' read unique code and quantity into dictionary
            arr(dic.Count, 1) = cell.Offset(0, 1).Value ' read item
            arr(dic.Count, 3) = cell.Offset(0, 3).Value ' read cost
        Else
            qty = qty + cell.Offset(0, 2).Value
            dic(cell.Value) = qty ' read running quantity sum
        End If
    Next
End With
With Worksheets("Hardware")
    .Range("A8").Resize(dic.Count + 1, 6).ClearContents
    .Range("A8:D8").Value = Worksheets("Cabinets").Range("AK1:AN1").Value
    .Range("B9").Resize(dic.Count, 3).Value = arr
    .Range("A9").Resize(dic.Count, 1).Value = WorksheetFunction.Transpose(dic.keys)
    .Range("C9").Resize(dic.Count, 1).Value = WorksheetFunction.Transpose(dic.items)
    .Range("F9").Resize(dic.Count, 1).Formula = "= C9*D9"
    
End With
End Sub

Book1
	AK	AL	AM	AN
1	BO_code	BO_item	BO_qty	BO_cost
2	AA	Alpha	1	50
3	AA	Alpha	2	50
4	AA	Alpha	3	50
5	AA	Alpha	4	50
6	BB	Beta	5	90
7	CC	Celsius	6	70
8	DD	Delta	7	55
9	DD	Delta	8	55
10	DD	Delta	9	55
11	DD	Delta	10	55
12	EE	Elephan	11	60
Cabinets

Cell Formulas
Range	Formula
AM5:AM12	AM5	=AM4+1

Book1
	A	B	C	D	E	F
8	BO_code	BO_item	BO_qty	BO_cost
9	AA	Alpha	10	50		500
10	BB	Beta	5	90		450
11	CC	Celsius	6	70		420
12	DD	Delta	34	55		1870
13	EE	Elephan	11	60		660
Hardware

Cell Formulas
Range	Formula
F9:F13	F9	= C9*D9

 

[andymalan]

Dear Bebo 021999, thank you for your input, but something is misfiring.

warm regards
Andy

 

[bebo021999]

Sorry the image is too small that I could not see the details.

 

[andymalan]

Dear Bebo 021999, thank you for your input, but something is misfiring.

View attachment 64563
warm regards
Andy

So sorry, please see the following..