VBA copy row IF <=, AND if duplicate, DO not copy

[Weeble]

I'm trying to make an inventory model. let's see if I can explain it.
This is "Sheet1"


[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Article[/TD]
[TD="align: center"]Inventory[/TD]
[TD]stack[/TD]
[/TR]
[TR]
[TD]Fork[/TD]
[TD]500[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Spoon[/TD]
[TD]50[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]BBQ[/TD]
[TD]300[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Sallad[/TD]
[TD]23[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Computer[/TD]
[TD]6[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

I need a VBA macro that copies the first 2 columns IF 'Inventory' Collumn has a value of <= 50 , AND 'Stack' =1 .
So 'Article' and 'Inventory' row with a value of <= 50 AND stack =1. Copied to "Sheet2".

I will be doing this every day. So IF either in Column 'Article* already exists in "Sheet2" either overwrite that line with the new 'Inventory' number or Skip it, if overwrite is not possible.

Anyone able to help me with this? I have searched around for a while. But can't really find a macro that fits my needs. Any help would be very kind.

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] Inv_Update()
    [color=darkblue]Dim[/color] v [color=darkblue]As[/color] [color=darkblue]Variant[/color], i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]With[/color] CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        
        v = Sheets("Sheet2").Range("A1").CurrentRegion
        [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](v, 1)
            .Item(v(i, 1)) = v(i, 2)
        [color=darkblue]Next[/color] i
        
        v = Sheets("Sheet1").Range("A1").CurrentRegion
        [color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] [color=darkblue]UBound[/color](v, 1)
            [color=darkblue]If[/color] v(i, 2) <= 50 And v(i, 3) = 1 [color=darkblue]Then[/color] .Item(v(i, 1)) = v(i, 2)
        [color=darkblue]Next[/color] i
        
        Sheets("Sheet2").Range("A1").Resize(.Count) = Application.Transpose(.Keys)
        Sheets("Sheet2").Range("B1").Resize(.Count) = Application.Transpose(.Items)
        
    [color=darkblue]End[/color] [color=darkblue]With[/color]
        
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Weeble]

Try this...

[COLOR=darkblue]Sub[/COLOR] Inv_Update()
    [COLOR=darkblue]Dim[/COLOR] v [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        
        v = Sheets("Sheet2").Range("A1").CurrentRegion
        [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](v, 1)
            .Item(v(i, 1)) = v(i, 2)
        [COLOR=darkblue]Next[/COLOR] i
        
        v = Sheets("Sheet1").Range("A1").CurrentRegion
        [COLOR=darkblue]For[/COLOR] i = 2 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](v, 1)
            [COLOR=darkblue]If[/COLOR] v(i, 2) <= 50 And v(i, 3) = 1 [COLOR=darkblue]Then[/COLOR] .Item(v(i, 1)) = v(i, 2)
        [COLOR=darkblue]Next[/COLOR] i
        
        Sheets("Sheet2").Range("A1").Resize(.Count) = Application.Transpose(.Keys)
        Sheets("Sheet2").Range("B1").Resize(.Count) = Application.Transpose(.Items)
        
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Code gives me an error on this line

 For i = 1 To UBound(v, 1)

Runerror nr.13, incompatible types.

Mby I punched in something wrong?

 

[AlphaFrog]

Does your data on both Sheet1 and Sheet2 start at cell A1?
Did you change anything in the code?

 

[Weeble]

Does your data on both Sheet1 and Sheet2 start at cell A1?
Did you change anything in the code?

Sheet2 is totaly empty. in 'sheet1' Both inventory and stack if generated by a formula though if that causes the problem? Inventory has formula, =SUMIF , and Stack has =COUNTIF.

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] Inv_Update()
    [color=darkblue]Dim[/color] v [color=darkblue]As[/color] [color=darkblue]Variant[/color], i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]With[/color] CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        
        Sheets("Sheet2").Range("A1:B1") = Array("Article", "Inventory")
        v = Sheets("Sheet2").Range("A1").CurrentRegion
        [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](v, 1)
            .Item(v(i, 1)) = v(i, 2)
        [color=darkblue]Next[/color] i
        
        v = Sheets("Sheet1").Range("A1").CurrentRegion
        [color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] [color=darkblue]UBound[/color](v, 1)
            [color=darkblue]If[/color] v(i, 2) <= 50 And v(i, 3) = 1 [color=darkblue]Then[/color] .Item(v(i, 1)) = v(i, 2)
        [color=darkblue]Next[/color] i
        
        Sheets("Sheet2").Range("A1").Resize(.Count) = Application.Transpose(.Keys)
        Sheets("Sheet2").Range("B1").Resize(.Count) = Application.Transpose(.Items)
        
    [color=darkblue]End[/color] [color=darkblue]With[/color]
        
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Weeble]

Try this...

[COLOR=darkblue]Sub[/COLOR] Inv_Update()
    [COLOR=darkblue]Dim[/COLOR] v [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        
        Sheets("Sheet2").Range("A1:B1") = Array("Article", "Inventory")
        v = Sheets("Sheet2").Range("A1").CurrentRegion
        [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](v, 1)
            .Item(v(i, 1)) = v(i, 2)
        [COLOR=darkblue]Next[/COLOR] i
        
        v = Sheets("Sheet1").Range("A1").CurrentRegion
        [COLOR=darkblue]For[/COLOR] i = 2 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](v, 1)
            [COLOR=darkblue]If[/COLOR] v(i, 2) <= 50 And v(i, 3) = 1 [COLOR=darkblue]Then[/COLOR] .Item(v(i, 1)) = v(i, 2)
        [COLOR=darkblue]Next[/COLOR] i
        
        Sheets("Sheet2").Range("A1").Resize(.Count) = Application.Transpose(.Keys)
        Sheets("Sheet2").Range("B1").Resize(.Count) = Application.Transpose(.Items)
        
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Sorry for the late reply, but thank you kindly for the help!