Copy Paste couple columns to another sheet if value is in dictionary key

[skong]

Hi everyone,

I'm trying to loop through all the cell in column B in sheet 2 to see if they match the key in the dictionary. If it does, then it should copy the row to sheet 1.

This is what I currently have. I literally just started studying VBA last week. Sorry for the bad coding =p

Dim symboldic As New Scripting.dictionary
Dim adv As Long
adv = Sheets("Results").Cells(3, 3).value
Dim beta As Long
beta = Sheets("Results").Cells(4, 3).value
Dim klrow As Integer
klrow = Sheets("Group Positions ADV BETA").Range("K65536").End(xlUp).row
Dim i As Integer
Dim symbol As String


For i = 2 To klrow
symbol = Sheets("Group Positions ADV BETA").Cells(i, 11).value

If Sheets("Group Positions ADV BETA").Cells(i, 12) < adv Or Sheets("Group Positions ADV BETA").Cells(i, 13) > beta Then
On Error Resume Next
symboldic.Add symbol, Nothing
End If
Next i


Dim j As Integer
Dim resultlrow As Integer
Dim result As String
Dim blastrow As Range




Set blastrow = Sheets("Results").Range("B65536").End(xlUp)


resultlrow = Sheets("Account Positions").Range("B" & Rows.Count).End(xlUp).row


For j = 2 To resultlrow
result = Sheets("Account Positions").Cells(j, 2).value


If symboldic.Exists(result) = True Then
Sheets("Results").Range("B" & blastrow, "E" & blastrow).value = Sheets("Account Positions").Range(Cells(j, 1), Cells(j, 4).value)
End If
Next j



End Sub

 

[AlphaFrog]

    [color=darkblue]Dim[/color] symboldic [color=darkblue]As[/color] [color=darkblue]New[/color] Scripting.Dictionary
    [color=darkblue]Dim[/color] adv       [color=darkblue]As[/color] Long: adv = Sheets("Results").Cells(3, 3).Value
    [color=darkblue]Dim[/color] beta      [color=darkblue]As[/color] Long: beta = Sheets("Results").Cells(4, 3).Value
    [color=darkblue]Dim[/color] NextRow   [color=darkblue]As[/color] Long: [color=darkblue]Next[/color]Row = Sheets("Results").Range("B65536").End(xlUp).Row + 1
    [color=darkblue]Dim[/color] i         [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] v         [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    
    [color=darkblue]With[/color] Sheets("Group Positions ADV BETA")
        v = .Range("K2", .Range("K65536").End(xlUp)).Resize(, 3).Value
    End [color=darkblue]With[/color]
    [color=darkblue]For[/color] i = [color=darkblue]LBound[/color](v, 1) [color=darkblue]To[/color] [color=darkblue]UBound[/color](v, 1)
        [color=darkblue]If[/color] v(i, 2) < adv [color=darkblue]Or[/color] v(i, 3) > beta [color=darkblue]Then[/color] symboldic.Item(v(i, 1)) = ""
    Next i
    
    [color=darkblue]With[/color] Sheets("Account Positions")
        [color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] .Range("B" & Rows.Count).[color=darkblue]End[/color](xlUp).Row
            [color=darkblue]If[/color] symboldic.Exists(.Cells(i, 2).Value) [color=darkblue]Then[/color]
                Sheets("Results").Range("B" & NextRow).Resize(, 4).Value = .Cells(i, 1).Resize(, 4).Value
                [color=darkblue]Next[/color]Row = NextRow + 1
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        Next i
    End [color=darkblue]With[/color]

 

[skong]

Hi AlphaFrog,

Thank you very much for the clarifications. I was able to get my code to work by change the integer to long. Thank you again. Much appreciated!!!

Best Regards