Function countshit(d As Date, n As String, kol As Integer) As Single

'(d As Date, n As String,kol as double)
' Макрос1 Макрос

' Сочетание клавиш: Ctrl+t

Dim q(100) As Single
Dim tov(100) As String
Dim col(100) As Integer
Dim r As Integer
Dim V(100) As Single
Dim sum As Single
Dim iRange As Range
Dim m(100) As Single
Dim s As Single
'ищем дату на листе производства
Set iRange = Sheets("Производство").Cells.Find(what:=d, LookIn:=xlValues, lookat:=xlPart)

'собираем имена и количество выпущенных продуктов
For i = 1 To kol

If iRange = d Then

Set iRange = iRange.Offset(0, 1)
tov(i) = iRange
Set iRange = iRange.Offset(0, 1)
q(i) = iRange
Set iRange = iRange.Offset(1, -2)
Else: i = kol
End If
Next i

'ищем имена товаров в спецификации, собираем номера их столбцом
For i = 1 To kol

Set iRange = Sheets("спецификация").Cells.Find(what:=tov(i), LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False)
col(i) = iRange.Column

Next i

'ищем имя сырья и собираем номер его столбца
Set iRange = Sheets("спецификация").Cells.Find(what:=n, After:=ActiveCell, LookIn:=xlValues, lookat:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
r = iRange.Row

'перемножаем количество на норму
For i = 1 To kol
m(i) = Sheets("спецификация").Cells(r, col(i))
V(i) = m(i) * q(i)
Next i
'складываем произведения
For i = 1 To kol
s = s + V(i)
Next i

countshit = s
End Function