Sub update()
' =IF(ABS(E10)<0.005,"",IF(E10<0,"负",)&IF(INT(ABS(E10)),TEXT(INT(ABS(E10)),"[dbnum2]")&"圆",)&IF(INT(ABS(E10)*10)-INT(ABS(E10))*10,TEXT(INT(ABS(E10)*10)-INT(ABS(E10))*10,"[dbnum2]")&"角",IF(INT(ABS(E10))=ABS(E10),,IF(ABS(E10)<0.1,,"零")))&IF(ROUND(ABS(E10)*100-INT(ABS(E10)*10)*10,),TEXT(ROUND(ABS(E10)*100-INT(ABS(E10)*10)*10,),"[dbnum2]")&"分","整"))
y = 100
Dim power1(4), water1(6)
Dim power3(6), water3(12)
Dim power6(6), water6(11)
Dim moon, year, eday
Dim txt As String
Sheet7.Select
p6 = 1
p3 = 1
p1 = 1
w1 = 1
w3 = 1
w6 = 1
moon = Sheet7.Cells(2, 6).Value
year = Sheet7.Cells(2, 5).Value
If moon < 12 And moon > 0 Then
moon = moon + 1
ElseIf moon = 12 Then
moon = 1
year = year + 1
End If
If year Mod 4 = 0 And moon = 2 Then
eday = 29
ElseIf moon = 2 Then
eday = 28
ElseIf moon = 1 Or moon = 3 Or moon = 5 Or moon = 7 Or moon = 8 Or moon = 10 Or moon = 12 Then
eday = 31
Else
eday = 30
End If
Sheet7.Cells(2, 6) = moon
Sheet7.Cells(2, 5) = year
txt = "计费时段:" & year & "-" & moon & "-1至" & year & "-" & moon & "-" & eday
For i = 1 To y
If InStr(Cells(i, 2), "xxx") And InStr(Cells(i, 1), "电") Then
power6(p6) = Cells(i, 3)
p6 = p6 + 1
ElseIf InStr(Cells(i, 2), "xxx") And InStr(Cells(i, 1), "电") Then
power3(p3) = Cells(i, 3)
p3 = p3 + 1
ElseIf InStr(Cells(i, 2), "xxx") And InStr(Cells(i, 1), "电") Then
power1(p1) = Cells(i, 3)
p1 = p1 + 1
ElseIf InStr(Cells(i, 2), "xxx") And InStr(Cells(i, 1), "水") Then
water6(w6) = Cells(i, 3)
w6 = w6 + 1
ElseIf InStr(Cells(i, 2), "xxx") And InStr(Cells(i, 1), "水") Then
water3(w3) = Cells(i, 3)
w3 = w3 + 1
ElseIf InStr(Cells(i, 2), "xxx") And InStr(Cells(i, 1), "水") Then
water1(w1) = Cells(i, 3)
w1 = w1 + 1
End If
Next
For j = 1 To 6
k6 = 1
k3 = 1
k1 = 1
w1 = 1
w3 = 1
w6 = 1
Sheets(j).Select
If Sheets(j).Name = "xxx" Then
For i = 1 To y
If InStr(Cells(i, 2), "xxx") Then
Sheets(j).Cells(i, 2) = txt
ElseIf (InStr(Cells(i, 1), "xxx") = 0) And InStr(Cells(i, 1), "电") Or InStr(Cells(i, 1), "水") Then
Cells(i, 3) = Cells(i, 2)
If (InStr(Cells(i, 1), "xxx") = 0) And InStr(Cells(i, 1), "电") And (InStr(Cells(i, 1), "表") = 0) Then
Cells(i, 2) = power1(k1)
k1 = k1 + 1
ElseIf (InStr(Cells(i, 1), "xxx") = 0) And InStr(Cells(i, 1), "水") Then
Cells(i, 2) = water1(w1)
w1 = w1 + 1
End If
End If
Next
ElseIf Sheets(j).Name = "xxx" Then
For i = 1 To y
If InStr(Cells(i, 2), "xxx") Then
Sheets(j).Cells(i, 2) = txt
ElseIf (InStr(Cells(i, 1), "大写") = 0) And InStr(Cells(i, 1), "电") Or InStr(Cells(i, 1), "水") Then
Cells(i, 3) = Cells(i, 2)
If (InStr(Cells(i, 1), "大写") = 0) And InStr(Cells(i, 1), "电") Then
Cells(i, 2) = power3(k3)
k3 = k3 + 1
ElseIf (InStr(Cells(i, 1), "大写") = 0) And InStr(Cells(i, 1), "水") Then
Cells(i, 2) = water3(w3)
w3 = w3 + 1
End If
End If
Next
ElseIf Sheets(j).Name = "xxx" Then
For i = 1 To y
If InStr(Cells(i, 2), "xxx") Then
Sheets(j).Cells(i, 2) = txt
ElseIf (InStr(Cells(i, 1), "大写") = 0) And InStr(Cells(i, 1), "电") Or InStr(Cells(i, 1), "水") Then
Cells(i, 3) = Cells(i, 2)
If (InStr(Cells(i, 1), "大写") = 0) And InStr(Cells(i, 1), "电") Then
Cells(i, 2) = power6(k6)
k6 = k6 + 1
ElseIf (InStr(Cells(i, 1), "大写") = 0) And InStr(Cells(i, 1), "水") Then
Cells(i, 2) = water6(w6)
w6 = w6 + 1
End If
End If
Next
ElseIf Sheets(j).Name = "每月应收明细表" Then
For i = 1 To y
If InStr(Cells(i, 2), "xxx") Then
' Dim txt1 As String
If moon = 1 Then
Cells(i, 3) = "12月水电"
Else
Cells(i, 3) = moon - 1 & "月水电"
End If
Cells(i, 2) = moon & "月房租"
Cells(i, 4) = moon & "月垃圾费"
End If
Next
ElseIf Sheets(j).Name = "对比表" Then
If moon = 1 Then
Cells(1, 1) = year - 1 & "年" & "12月水电对比表"
Else
Cells(1, 1) = year & "年" & moon - 1 & "月水电对比表"
End If
End If
Next
Sheet1.Select
' MsgBox (Sheet1.Cells(1, 2))
End Sub
