Dictionary item modified before storning-CodePudding

I want to copy a filtered column from one worksheet to another. The visible values of the filtered list in sheet "samples" being stored in a dictionary and then copied in sheet "output".

This works well, but I am struggling with a modification of value "quantity" in "samples" column G before storing in the dictionary. The condition is if this corresponding value in column C is equivalent to string "sell" then the "quantity" value must be set as negative (-n).

The below code produces an error "Runtime error 13 - Type mismatch". Any idea how to solve?

Sub FilterAndCopy_C()

Dim wsData      As Worksheet
Dim dws         As Worksheet
Dim lr          As Long
Dim x           As Variant
Dim y           As Variant
Dim dict        As Object
Dim i           As Long
   
Set wsData = Worksheets("Samples")
Set dws = Worksheets("Output")

lr = wsData.Cells(Rows.Count, "A").End(xlUp).Row
x = wsData.Range("G5:G" & lr).Value 'Quantity
y = wsData.Range("C5:C" & lr).Value 'Order type

Set dict = CreateObject("Scripting.Dictionary")

For i = 1 To UBound(x, 1)
    
    If y = "Sell" Then
    dict.Item(x(i, 1)) = "-" & ""
        
    Else
    dict.Item(x(i, 1)) = ""
    End If
Next i

For Each it In dict.Keys
    With wsData.Range("G5").CurrentRegion
        wsData.Range("G5:G" & lr).SpecialCells(xlCellTypeVisible).Copy dws.Range("C2")
    End With        
Next it

End Sub

CodePudding user response：

Here is something that does what I think you want to do:

Sub FilterAndCopy_C()

Dim wsData      As Worksheet
Dim dws         As Worksheet
Dim lr          As Long
Dim x           As Variant
Dim y           As Variant
Dim dict        As Object
Dim i           As Long
   
Set wsData = Worksheets("Samples")
Set dws = Worksheets("Output")

lr = wsData.Cells(Rows.Count, "A").End(xlUp).Row
x = wsData.Range("G5:G" & lr).Value 'Quantity
y = wsData.Range("C5:C" & lr).Value 'Order type

Set dict = CreateObject("Scripting.Dictionary")

For i = 1 To UBound(x, 1)
    If wsData.Range("G" & 5 - 1   i).EntireRow.Hidden = False Then
        If y(i, 1) = "Sell" Then
            dict.Add i, -x(i, 1)
        Else
            dict.Add i, x(i, 1)
        End If
    End If
Next i

Dim k As Variant
For Each k In dict.Keys '1-based
    j = j   1
    dws.Range("C2").Offset(k - 1).Value = dict.items()(j - 1) '.Items() returns a 0-based Variant array
Next

End Sub

No need for a loop Paste, just a For Each...Next on the dictionary entries to write them on the "Output" sheet is enough.

CodePudding user response：

To clarify with some screenshots what I want to accomplish (with my limited vba skills): copy the values of a filtered list in sheet "samples" column G to "output" sheet's column C with the above mentioned modification, i.e. change quantity to negative e.g. -25 from 25 if type is "sell". The purpose of this exercise is a FIFO calculation on filtered items.

The above proposed code applies correctly the negative values but adds blank lines in the output sheet, i.e. hidden rows from the autofilter.

I appreciate your help.

Input data sheet "samples"

Current incorrect result on sheet "outcome"

Expected result