Gửi các anh chị,
Em mới đc hướng dẫn để thao tác Macro cho tiện công việc, học kiểu đi tắt đón đầu, ko đến nơi đến chốn, giờ đang gặp trục trặc, em cũng chưa biết sửa sao.
Anh chị nào có thể chỉ giáo giúp em với ạ. Tất cả đều ổn trừ cái dòng em bôi đậm bị báo lỗi ạ.
Sub rental()
Worksheets("Rental").Select
Call clear
iend = 0
iend = ActiveCell.SpecialCells(xlLastCell).Row
For i = 6 To iend Step 1
If Worksheets("Rental").Cells(i, 1).Value <> "" And Worksheets("Rental").Cells(i + 1, 1).Value = "" Then
k = i
ElseIf Worksheets("Rental").Cells(i, 1).Value = "" And Worksheets("Rental").Cells(i + 1, 1).Value <> "" Then
m = i
x = m - k
Call calculate(k, x)
'Call calculate(6, 1)
ElseIf Worksheets("Rental").Cells(i, 1).Value <> "" And Worksheets("Rental").Cells(i + 1, 1).Value <> "" Then
k = i
Call calculate(k, 0)
End If
Next
Call Find_n_month_rental
Call monthly_fee
Beep
End Sub
Sub calculate(k, x)
'Worksheets("Rental").Select
h = 0
iend = 0
iend = ActiveCell.SpecialCells(xlLastCell).Row
For h = 0 To x Step 1
company_code = Worksheets("Rental").Cells(k, 1).Value
company_name = Worksheets("Rental").Cells(k, 2).Value
machine_code = Worksheets("Rental").Cells(k + h, 5).Value
'Worksheets("Price list").Select
For j = 120 To 200 Step 1
' MsgBox Worksheets("Price list").Cells(j, 1).Value
If Worksheets("Price list").Cells(j, 1).Value = company_code Then
For f = 12 To 19 Step 1
'MsgBox Worksheets("Price list").Cells(121, f).Value
If Worksheets("Price list").Cells(121, f).Value = machine_code Then
' MsgBox machine_code
'MsgBox Worksheets("Price list").Cells(j, f).Value
Worksheets("Rental").Cells(h + k, 7).Value = Worksheets("Price list").Cells(j, f).Value * Worksheets("Rental").Cells(h + k, 6).Value
Worksheets("Rental").Cells(h + k, 10).Value = Worksheets("Price list").Cells(j, 3).Value
End If
Next
For e = 4 To 11 Step 1
If Worksheets("Price list").Cells(121, e).Value = machine_code Then
'MsgBox machine_code
'MsgBox Worksheets("Price list").Cells(j, f).Value
Worksheets("Rental").Cells(h + k, 8).Value = Worksheets("Price list").Cells(j, e).Value * Worksheets("Rental").Cells(h + k, 6).Value
End If
Next
For v = 20 To 27 Step 1
If Worksheets("Price list").Cells(121, v).Value = machine_code Then
'MsgBox machine_code
'MsgBox Worksheets("Price list").Cells(j, f).Value
Worksheets("Rental").Cells(h + k, 9).Value = Worksheets("Price list").Cells(j, v).Value
End If
Next
End If
Next
Next
End Sub
Sub monthly_fee()
Action = False
'Worksheets("Rental").Select
iend = 0
iend = ActiveCell.SpecialCells(xlLastCell).Row
For i = 6 To iend Step 1
If Right(Worksheets("Rental").Cells(i, 10).Value, 2) <> "" Then
month_number = Right(Worksheets("Rental").Cells(i, 10).Value, 2)
j = i
'MsgBox m & "__" & y
Call calculate_month(j, month_number)
End If
Next
End Sub
Sub calculate_month(r, month_number)
'MsgBox m & "_" & y
m = Month(Worksheets("Rental").Cells(r, 4).Value)
y = Year(Worksheets("Rental").Cells(r, 4).Value)
If Worksheets("Rental").Cells(r, 4).Value <> "" Then
For i = 11 To 200 Step 1
If Worksheets("Rental").Cells(5, i).Value <> "" Then
M1 = Month(Worksheets("Rental").Cells(5, i).Value)
y1 = Year(Worksheets("Rental").Cells(5, i).Value)
If M1 = m And y1 = y Then
'MsgBox i & "_" & j
'month_number = Right(Worksheets("Rental").Cells(i, 10).Value, 2)
For u = i To i + month_number - 1 Step 1
Worksheets("Rental").Cells(r, u + 1).Value = Worksheets("Rental").Cells(r, 9).Value * Worksheets("Rental").Cells(r, 6).Value
Next
End If
End If
Next
End If
If Worksheets("Rental").Cells(r, 4).Value = "" And Worksheets("Rental").Cells(r - 1, 4).Value <> "" Then
m = Month(Worksheets("Rental").Cells(r - 1, 4).Value)
y = Year(Worksheets("Rental").Cells(r - 1, 4).Value)
For i = 11 To 100 Step 1
If Worksheets("Rental").Cells(5, i).Value <> "" Then
M1 = Month(Worksheets("Rental").Cells(5, i).Value)
y1 = Year(Worksheets("Rental").Cells(5, i).Value)
If M1 = m And y1 = y Then
'MsgBox i & "_" & j
'month_number = Right(Worksheets("Rental").Cells(i, 10).Value, 2)
For u = i To i + month_number - 1 Step 1
Worksheets("Rental").Cells(r, u + 1).Value = Worksheets("Rental").Cells(r, 9).Value * Worksheets("Rental").Cells(r, 6).Value
Next
End If
End If
Next
End If
If Worksheets("Rental").Cells(r, 4).Value = "" And Worksheets("Rental").Cells(r - 1, 4).Value = "" And Worksheets("Rental").Cells(r - 1, 4).Value <> "" Then
m = Month(Worksheets("Rental").Cells(r - 2, 4).Value)
y = Year(Worksheets("Rental").Cells(r - 2, 4).Value)
For i = 11 To 100 Step 1
If Worksheets("Rental").Cells(5, i).Value <> "" Then
M1 = Month(Worksheets("Rental").Cells(5, i).Value)
y1 = Year(Worksheets("Rental").Cells(5, i).Value)
If M1 = m And y1 = y Then
'MsgBox i & "_" & j
'month_number = Right(Worksheets("Rental").Cells(i, 10).Value, 2)
For u = i To i + month_number - 1 Step 1
Worksheets("Rental").Cells(r, u + 1).Value = Worksheets("Rental").Cells(r, 9).Value * Worksheets("Rental").Cells(r, 6).Value
Next
End If
End If
Next
End If
If Worksheets("Rental").Cells(r, 4).Value = "" And Worksheets("Rental").Cells(r - 1, 4).Value = "" And Worksheets("Rental").Cells(r - 1, 4).Value = "" And Worksheets("Rental").Cells(r - 2, 4).Value <> "" Then
m = Month(Worksheets("Rental").Cells(r - 3, 4).Value)
y = Year(Worksheets("Rental").Cells(r - 3, 4).Value)
For i = 11 To 100 Step 1
If Worksheets("Rental").Cells(5, i).Value <> "" Then
M1 = Month(Worksheets("Rental").Cells(5, i).Value)
y1 = Year(Worksheets("Rental").Cells(5, i).Value)
If M1 = m And y1 = y Then
'MsgBox i & "_" & j
'month_number = Right(Worksheets("Rental").Cells(i, 10).Value, 2)
For u = i To i + month_number - 1 Step 1
Worksheets("Rental").Cells(r, u + 1).Value = Worksheets("Rental").Cells(r, 9).Value * Worksheets("Rental").Cells(r, 6).Value
Next
End If
End If
Next
End If
End Sub
Sub clear()
Worksheets("Rental").Select
Range("G6:J47").Select
Selection.ClearContents
Range("K5:LV47").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("K5:XFD47").Select
Selection.ClearContents
End Sub
Sub Find_n_month_rental()
Worksheets("Rental").Select
iend = 0
iend = ActiveCell.SpecialCells(xlLastCell).Row
n_month = 13
n_year = 2020
'MsgBox n_month & "__" & n_year
For i = 6 To iend Step 1
If Worksheets("Rental").Cells(i, 4).Value <> "" And Year(Worksheets("Rental").Cells(i, 4).Value) < n_year Then
n_year = Year(Worksheets("Rental").Cells(i, 4).Value)
'monthyear = Worksheets("Rental").Cells(i, 4).Value
End If
Next
For i = 6 To iend Step 1
If Worksheets("Rental").Cells(i, 4).Value <> "" And Year(Worksheets("Rental").Cells(i, 4).Value) = n_year Then
If Month(Worksheets("Rental").Cells(i, 4).Value) < n_month Then
n_month = Month(Worksheets("Rental").Cells(i, 4).Value)
'monthyear = Worksheets("Rental").Cells(i, 4).Value
k = i
End If
End If
Next
'MsgBox n_month
colum_length = 11
y = 11
mean_year = 2011
lenth = 1
For i = 6 To iend Step 1
If Worksheets("Rental").Cells(i, 4).Value <> "" Then
a = Year(Worksheets("Rental").Cells(i, 4).Value) - mean_year
month_length = Right(Worksheets("Rental").Cells(i, 10).Value, 2) + Month(Worksheets("Rental").Cells(i, 4).Value) + 12 * a
If month_length > Length Then
Length = Right(Worksheets("Rental").Cells(i, 10).Value, 2) + Month(Worksheets("Rental").Cells(i, 4).Value) + 12 * a
e = i
End If
'MsgBox a
End If
Next
colum_length = Length + y - n_month
n = 0
m = 0
For j = 11 To colum_length Step 1
If Month(Worksheets("Rental").Cells(k, 4).Value) + n <= 12 Then
Worksheets("Rental").Cells(5, j).Value = Month(Worksheets("Rental").Cells(k, 4).Value) + n & "-" & Year(Worksheets("Rental").Cells(k, 4).Value)
n = n + 1
'MsgBox n
ElseIf Month(Worksheets("Rental").Cells(k, 4).Value) + n > 12 Then
Worksheets("Rental").Cells(5, j).Value = 1 + m & "-" & Year(Worksheets("Rental").Cells(k, 4).Value) + 1 + l
m = m + 1
If m = 12 Then
m = 0
l = l + 1
End If
End If
Next
End Sub
Em mới đc hướng dẫn để thao tác Macro cho tiện công việc, học kiểu đi tắt đón đầu, ko đến nơi đến chốn, giờ đang gặp trục trặc, em cũng chưa biết sửa sao.
Anh chị nào có thể chỉ giáo giúp em với ạ. Tất cả đều ổn trừ cái dòng em bôi đậm bị báo lỗi ạ.
Sub rental()
Worksheets("Rental").Select
Call clear
iend = 0
iend = ActiveCell.SpecialCells(xlLastCell).Row
For i = 6 To iend Step 1
If Worksheets("Rental").Cells(i, 1).Value <> "" And Worksheets("Rental").Cells(i + 1, 1).Value = "" Then
k = i
ElseIf Worksheets("Rental").Cells(i, 1).Value = "" And Worksheets("Rental").Cells(i + 1, 1).Value <> "" Then
m = i
x = m - k
Call calculate(k, x)
'Call calculate(6, 1)
ElseIf Worksheets("Rental").Cells(i, 1).Value <> "" And Worksheets("Rental").Cells(i + 1, 1).Value <> "" Then
k = i
Call calculate(k, 0)
End If
Next
Call Find_n_month_rental
Call monthly_fee
Beep
End Sub
Sub calculate(k, x)
'Worksheets("Rental").Select
h = 0
iend = 0
iend = ActiveCell.SpecialCells(xlLastCell).Row
For h = 0 To x Step 1
company_code = Worksheets("Rental").Cells(k, 1).Value
company_name = Worksheets("Rental").Cells(k, 2).Value
machine_code = Worksheets("Rental").Cells(k + h, 5).Value
'Worksheets("Price list").Select
For j = 120 To 200 Step 1
' MsgBox Worksheets("Price list").Cells(j, 1).Value
If Worksheets("Price list").Cells(j, 1).Value = company_code Then
For f = 12 To 19 Step 1
'MsgBox Worksheets("Price list").Cells(121, f).Value
If Worksheets("Price list").Cells(121, f).Value = machine_code Then
' MsgBox machine_code
'MsgBox Worksheets("Price list").Cells(j, f).Value
Worksheets("Rental").Cells(h + k, 7).Value = Worksheets("Price list").Cells(j, f).Value * Worksheets("Rental").Cells(h + k, 6).Value
Worksheets("Rental").Cells(h + k, 10).Value = Worksheets("Price list").Cells(j, 3).Value
End If
Next
For e = 4 To 11 Step 1
If Worksheets("Price list").Cells(121, e).Value = machine_code Then
'MsgBox machine_code
'MsgBox Worksheets("Price list").Cells(j, f).Value
Worksheets("Rental").Cells(h + k, 8).Value = Worksheets("Price list").Cells(j, e).Value * Worksheets("Rental").Cells(h + k, 6).Value
End If
Next
For v = 20 To 27 Step 1
If Worksheets("Price list").Cells(121, v).Value = machine_code Then
'MsgBox machine_code
'MsgBox Worksheets("Price list").Cells(j, f).Value
Worksheets("Rental").Cells(h + k, 9).Value = Worksheets("Price list").Cells(j, v).Value
End If
Next
End If
Next
Next
End Sub
Sub monthly_fee()
Action = False
'Worksheets("Rental").Select
iend = 0
iend = ActiveCell.SpecialCells(xlLastCell).Row
For i = 6 To iend Step 1
If Right(Worksheets("Rental").Cells(i, 10).Value, 2) <> "" Then
month_number = Right(Worksheets("Rental").Cells(i, 10).Value, 2)
j = i
'MsgBox m & "__" & y
Call calculate_month(j, month_number)
End If
Next
End Sub
Sub calculate_month(r, month_number)
'MsgBox m & "_" & y
m = Month(Worksheets("Rental").Cells(r, 4).Value)
y = Year(Worksheets("Rental").Cells(r, 4).Value)
If Worksheets("Rental").Cells(r, 4).Value <> "" Then
For i = 11 To 200 Step 1
If Worksheets("Rental").Cells(5, i).Value <> "" Then
M1 = Month(Worksheets("Rental").Cells(5, i).Value)
y1 = Year(Worksheets("Rental").Cells(5, i).Value)
If M1 = m And y1 = y Then
'MsgBox i & "_" & j
'month_number = Right(Worksheets("Rental").Cells(i, 10).Value, 2)
For u = i To i + month_number - 1 Step 1
Worksheets("Rental").Cells(r, u + 1).Value = Worksheets("Rental").Cells(r, 9).Value * Worksheets("Rental").Cells(r, 6).Value
Next
End If
End If
Next
End If
If Worksheets("Rental").Cells(r, 4).Value = "" And Worksheets("Rental").Cells(r - 1, 4).Value <> "" Then
m = Month(Worksheets("Rental").Cells(r - 1, 4).Value)
y = Year(Worksheets("Rental").Cells(r - 1, 4).Value)
For i = 11 To 100 Step 1
If Worksheets("Rental").Cells(5, i).Value <> "" Then
M1 = Month(Worksheets("Rental").Cells(5, i).Value)
y1 = Year(Worksheets("Rental").Cells(5, i).Value)
If M1 = m And y1 = y Then
'MsgBox i & "_" & j
'month_number = Right(Worksheets("Rental").Cells(i, 10).Value, 2)
For u = i To i + month_number - 1 Step 1
Worksheets("Rental").Cells(r, u + 1).Value = Worksheets("Rental").Cells(r, 9).Value * Worksheets("Rental").Cells(r, 6).Value
Next
End If
End If
Next
End If
If Worksheets("Rental").Cells(r, 4).Value = "" And Worksheets("Rental").Cells(r - 1, 4).Value = "" And Worksheets("Rental").Cells(r - 1, 4).Value <> "" Then
m = Month(Worksheets("Rental").Cells(r - 2, 4).Value)
y = Year(Worksheets("Rental").Cells(r - 2, 4).Value)
For i = 11 To 100 Step 1
If Worksheets("Rental").Cells(5, i).Value <> "" Then
M1 = Month(Worksheets("Rental").Cells(5, i).Value)
y1 = Year(Worksheets("Rental").Cells(5, i).Value)
If M1 = m And y1 = y Then
'MsgBox i & "_" & j
'month_number = Right(Worksheets("Rental").Cells(i, 10).Value, 2)
For u = i To i + month_number - 1 Step 1
Worksheets("Rental").Cells(r, u + 1).Value = Worksheets("Rental").Cells(r, 9).Value * Worksheets("Rental").Cells(r, 6).Value
Next
End If
End If
Next
End If
If Worksheets("Rental").Cells(r, 4).Value = "" And Worksheets("Rental").Cells(r - 1, 4).Value = "" And Worksheets("Rental").Cells(r - 1, 4).Value = "" And Worksheets("Rental").Cells(r - 2, 4).Value <> "" Then
m = Month(Worksheets("Rental").Cells(r - 3, 4).Value)
y = Year(Worksheets("Rental").Cells(r - 3, 4).Value)
For i = 11 To 100 Step 1
If Worksheets("Rental").Cells(5, i).Value <> "" Then
M1 = Month(Worksheets("Rental").Cells(5, i).Value)
y1 = Year(Worksheets("Rental").Cells(5, i).Value)
If M1 = m And y1 = y Then
'MsgBox i & "_" & j
'month_number = Right(Worksheets("Rental").Cells(i, 10).Value, 2)
For u = i To i + month_number - 1 Step 1
Worksheets("Rental").Cells(r, u + 1).Value = Worksheets("Rental").Cells(r, 9).Value * Worksheets("Rental").Cells(r, 6).Value
Next
End If
End If
Next
End If
End Sub
Sub clear()
Worksheets("Rental").Select
Range("G6:J47").Select
Selection.ClearContents
Range("K5:LV47").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("K5:XFD47").Select
Selection.ClearContents
End Sub
Sub Find_n_month_rental()
Worksheets("Rental").Select
iend = 0
iend = ActiveCell.SpecialCells(xlLastCell).Row
n_month = 13
n_year = 2020
'MsgBox n_month & "__" & n_year
For i = 6 To iend Step 1
If Worksheets("Rental").Cells(i, 4).Value <> "" And Year(Worksheets("Rental").Cells(i, 4).Value) < n_year Then
n_year = Year(Worksheets("Rental").Cells(i, 4).Value)
'monthyear = Worksheets("Rental").Cells(i, 4).Value
End If
Next
For i = 6 To iend Step 1
If Worksheets("Rental").Cells(i, 4).Value <> "" And Year(Worksheets("Rental").Cells(i, 4).Value) = n_year Then
If Month(Worksheets("Rental").Cells(i, 4).Value) < n_month Then
n_month = Month(Worksheets("Rental").Cells(i, 4).Value)
'monthyear = Worksheets("Rental").Cells(i, 4).Value
k = i
End If
End If
Next
'MsgBox n_month
colum_length = 11
y = 11
mean_year = 2011
lenth = 1
For i = 6 To iend Step 1
If Worksheets("Rental").Cells(i, 4).Value <> "" Then
a = Year(Worksheets("Rental").Cells(i, 4).Value) - mean_year
month_length = Right(Worksheets("Rental").Cells(i, 10).Value, 2) + Month(Worksheets("Rental").Cells(i, 4).Value) + 12 * a
If month_length > Length Then
Length = Right(Worksheets("Rental").Cells(i, 10).Value, 2) + Month(Worksheets("Rental").Cells(i, 4).Value) + 12 * a
e = i
End If
'MsgBox a
End If
Next
colum_length = Length + y - n_month
n = 0
m = 0
For j = 11 To colum_length Step 1
If Month(Worksheets("Rental").Cells(k, 4).Value) + n <= 12 Then
Worksheets("Rental").Cells(5, j).Value = Month(Worksheets("Rental").Cells(k, 4).Value) + n & "-" & Year(Worksheets("Rental").Cells(k, 4).Value)
n = n + 1
'MsgBox n
ElseIf Month(Worksheets("Rental").Cells(k, 4).Value) + n > 12 Then
Worksheets("Rental").Cells(5, j).Value = 1 + m & "-" & Year(Worksheets("Rental").Cells(k, 4).Value) + 1 + l
m = m + 1
If m = 12 Then
m = 0
l = l + 1
End If
End If
Next
End Sub