VBA to enter value of one cell into corresponding cells in the same worksheet

Hello Guys,

I just started with VBA so i am no expert. So basically i am trying to do is have a vba code that will take cell "A38" match that cell(item Code) with column(Item Code) "B13:B28" and than once it finds the matching cell record the cell value (price) on "C38" with the corresponding column (price) "E13:E28" if no value to say "for quotation". I attached a sample worksheet so you can see what i mean.

forum.ozgrid.com/index.php?attachment/68902/forum.ozgrid.com/index.php?attachment/68902/

Re: VBA to enter value of one cell into corresponding cells in the same worksheet

It looks good. Looks like the values are populating from the Sandvik US price list which is fine. I think what i really wanted to achieve in worksheet 2 is to use the search button look for the item in the Sandvik US price list and have the price show in the table. The reason for this is that it will only show the prices for the items that the customer want. Let me know if there is anything else we can do?

Thank You

Re: VBA to enter value of one cell into corresponding cells in the same worksheet

Quote from Raha;769536

See if this works for you...

Code

Sub Test()
    Dim SL As Object, A As Variant, i As Long
    
    Set SL = CreateObject("system.collections.sortedlist")
    With Sheets("Sandvik US Price List").Cells(1).CurrentRegion
        A = .Value
        For i = 2 To UBound(A, 1)
            If Not SL.contains(A(i, 1)) Then
                SL.Item(A(i, 1)) = VBA.Array(A(i, 2), A(i, 3))
            End If
        Next
    End With
    
    With Sheets("Sheet2").Cells(12, 1).CurrentRegion
        A = .Value
        .ClearContents
        For i = 2 To UBound(A, 1)
            If SL.contains(A(i, 2)) Then
                A(i, 4) = SL(A(i, 2))(0): A(i, 5) = SL(A(i, 2))(1)
            Else
                A(i, 5) = "For quotation"
            End If
        Next
        .Value = A
    End With
End Sub

Display More

*Or Can we do the same code for different spreadsheets? I will be adding new sheets all the prices will be updating from the same list. See attached spreadsheet i added sheet3 with new products. Can we do the same in sheet2 as in sheet3 and sheet 4,5,6 in the future?

Thank You for your help.forum.ozgrid.com/index.php?attachment/68906/