EDITAR:
Eu não entendi a pergunta. A seguir está o código modificado para calcular a soma puramente no VBA sem gravar os valores individuais para a planilha:
Sub Test()
Dim rng As Range, cell As Range
Dim Val1 As Variant
Dim Sum As Double: Sum = 0
Set rng = Range("K1:K10")
For Each cell In rng
Val1 = cell.Value
If Val1 = "Item1" Then
Sum = Sum + cell.Offset(rowOffset:=0, columnOffset:=1).Value
End If
Next cell
Range("N11").Value2 = Sum
End Sub
A partir da resposta original:
Uma Regra de Ouro realmente importante que você precisa aprender sobre programação em VBA é Nunca use Ativar e ActiveCell .
Eu refatorei seu código para mostrar como fazer isso (além de demonstrar mais algumas dicas):
Sub Test()
Dim cell As Range
For Each cell In Range("K1:K10")
Dim Val1 As Variant
Val1 = cell.Value2
If Val1 = "Item1" Then
Dim Val2 As Variant
Val2 = cell.Offset(rowOffset:=0, columnOffset:=1).Value2
If Sheets("Sheet1").Range("N1") = "" Then
Sheets("Sheet1").Range("N1") = Val2
Else
Sheets("Sheet1").Range("N33").End(xlUp).Offset(1) = Val2
End If
End If
Next cell
Range("N11").FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
End Sub
Então, esta linha:
Val2 = ActiveCell.Offset(rowOffset:=0, ColumnOffset:=1).Value
Altera para:
Val2 = cell.Offset(rowOffset:=0, ColumnOffset:=1).Value
Ou melhor:
Val2 = cell.Offset(ColumnOffset:=1).Value2