Snasui Excel Forum

Code: Select all

Private Sub CommandButton1_Click()
  Dim test As Range
  Dim i As Integer
  Dim price() As Variant
  
  Size = Cells(Rows.Count, "G").End(xlUp).Row
  
  ReDim price(Size)
  
  Set test = Range("A2:C21")
  
  For i = 2 To Size
        
        price(i) = Application.WorksheetFunction.VLookup(Cells(i, 7).Value, Range("A2:C21"), 3, 0)
        Cells(i, 8).Value = price(i)
        
  Next

End Sub

Code: Select all

Private Sub CommandButton1_Click()
  Dim test As Range
  Dim i As Integer
  Dim price() As Variant
  
  Size = Cells(Rows.Count, "G").End(xlUp).Row
  
  ReDim price(Size)
  
  Set test = Range("A2:C100")
  
  For i = 2 To Size
            If Application.WorksheetFunction.CountIf(Sheets(Sheet1.Name).Range("A:A"), Cells(i, 7).Value) = 0 Then
            Cells(i, 8).Value = "Error"
            Else
        price(i) = Application.WorksheetFunction.VLookup(Cells(i, 7).Value, Range("A2:C100"), 3, 0)
        Cells(i, 8).Value = price(i)
            End If
  Next

End Sub