Íóæí âñòàâ ïîëüç ôóíê â ìàêðîñ, ñîõð êàê *.xlsm. Â ëèñòå êàëüê â "òàðèô" âûçûâàòü tarif. Ëèñòû ñ öåíàìè äîëæ áûòü "Ïðîäóêò (ïóòü)".
Public Function tarif(Path As String, Product As String, plotnost As Long) As Double
Dim mySheet As Worksheet
gh = Product + " (" + Path + ")"
On Error Resume Next
Set mySheet = ActiveWorkbook.Worksheets(gh)
SheetExist = Not mySheet Is Nothing
If SheetExist Then
With mySheet
For i = 4 To 20
If InStr(Trim(.Cells(i, "A").Value), " ") > 0 And InStr(Trim(.Cells(i, "B").Value), " ") < 1 Then
df = CLng(Split(Trim(.Cells(i, "A").Value), " ")(0))
df1 = CLng(1000000)
ElseIf InStr(Trim(.Cells(i, "A").Value), "-") > 0 And InStr(Trim(.Cells(i, "B").Value), " ") < 1 Then
df = CLng(Split(Trim(.Cells(i, "A").Value), "-")(0))
df1 = CLng(Split(Trim(.Cells(i, "A").Value), "-")(1))
Else
df = CLng(Split(Trim(.Cells(i, "A").Value), " ")(0))
df1 = CLng(0)
End If
If InStr(Trim(.Cells(i, "B").Value), " ") < 1 And df < plotnost And df1 > plotnost Then
sd1 = i
ElseIf InStr(Trim(.Cells(i, "B").Value), " ") > 0 And df > plotnost And df1 < plotnost Then
sd1 = i
End If
Next
If InStr(Trim(.Cells(sd1, "B").Value), " ") < 1 Then
Debug.Print CDbl(Trim(mySheet.Cells(sd1, "B").Value))
tarif = CDbl(Trim(mySheet.Cells(sd1, "B").Value))
ElseIf InStr(Trim(.Cells(sd1, "B").Value), " ") > 0 Then
tarif = CDbl(Split(Trim(mySheet.Cells(sd1, "B").Value), " ")(0))
End If
End With
Else
MsgBox "Â òåêóùåé ñòðîêå Ïóòü èëè Òîâàð óêàçàíû íå âåðíî, èëè â òåêóùåì äîêóìåíòå íåò ëèñòà òàêîãî ïðàéñà!"
End If
End Function
Êàëüêóëÿòîð äîñòàâêè â Microsoft Excel
Ðåêëàìà
Êàëüêóëÿòîð äîñòàâêè â Microsoft Excel
Íåîáõîäèìî ñîçäàòü êàëüêóëÿòîð äîñòàâêè äëÿ ìåíåäæåðîâ, ãäå áóäóò ââîäèòñÿ äàííûå, è íà âûõîäå ïîëó÷àòü ñóììó äîñòàâêè. Ïîäðîáíîå ÒÇ ïðèêðåïèë â âèäåîôîðìàòå.