Excel 用程序高效制表（複雜測算及調試） 奕龍君2009/02/212020/07/240650 在制定價表（包括投資表）過程中需要複雜計算以及不斷調整計算結果，以期達到最佳效果，面對成千上萬個數字，多個條件設置，多個公式引用，拉表計算是低級運用，且易出錯，最高效，最安全的辦法就是用程序配合計算，這是我寫的excel VB程序代碼，附源碼，excel文件不提供，感興趣的自己嘗試，但至少要會使用excel的宏功能。 Sub direction() Dim temp For i = 4 To 195 temp = Range(“f” & i) Select Case temp Case “A1”, “F1”, “A2”, “F2”, “G1”, “G2” temp = “北” Case “C1” temp = “西” Case “B2”, “E1” temp = “西南” Case “B1”, “E2” temp = “东南” Case “C2” temp = “东” Case “D1”, “D2” temp = “南” End Select Range(“J” & i) = temp Range(“M” & i) = DirectionPrice(temp) Next End Sub Sub FloorPrice() Dim SFloor, EFloor, MFloor, PFloor, pricetemp MFloor = 7 For i = 4 To 195 PFloor = CLng(Range(“C” & i)) Select Case PFloor Case (PFloor = MFloor) pricetemp = 0 Case Else pricetemp = (PFloor – MFloor) * 30 End Select If PFloor = 4 Then pricetemp = pricetemp – 20 ‘4楼调整和3楼一样 If PFloor > MFloor + 1 Then pricetemp = pricetemp + 20 ‘8楼以上价格微调 If PFloor = 13 Then pricetemp = pricetemp – 20 ‘顶楼价格微调 Range(“L” & i) = pricetemp Next End Sub Sub ResidencePrice() Dim pResidence, pricetemp For i = 4 To 195 pResidence = Range(“F” & i) Select Case pResidence Case “D1”, “D2” pricetemp = 1.1 Case “C1”, “C2” pricetemp = 0.95 Case “E1”, “E2” pricetemp = 1.05 Case “B1”, “B2” pricetemp = 1.025 Case Else pricetemp = 1 End Select Range(“K” & i) = pricetemp Next End Sub Sub JGPrice() Dim pBuiding, pResidence, pricetemp For i = 4 To 195 pBuiding = Range(“B” & i) pResidence = Range(“F” & i) If pBuiding = “A” Then Select Case pResidence Case “F1”, “C1”, “E1” pricetemp = 98.85 Case “D1” pricetemp = 65 Case Else pricetemp = 0 End Select End If If pBuiding = “B” Then Select Case pResidence Case “F2”, “E2”, “C2” pricetemp = 98.85 Case “D2” pricetemp = 65 Case Else pricetemp = 0 End Select End If Range(“N” & i) = pricetemp Next End Sub Function DirectionPrice(direction) Dim pricetemp Select Case direction Case “北” pricetemp = 0 Case “东北”, “西北” pricetemp = 25 Case “东”, “西” pricetemp = 50 Case “西南”, “东南” pricetemp = 75 Case “南” pricetemp = 100 End Select DirectionPrice = pricetemp End Function Sub find2() Call findcancel Dim floor, rownum For i = 4 To 195 floor = Range(“C” & i) If (floor Mod 2 = 1) Then Rows(i).EntireRow.Hidden = True End If Next End Sub Sub find1() Call findcancel Dim floor For i = 4 To 195 floor = Range(“C” & i) If (floor Mod 2 = 0) Then Rows(i).EntireRow.Hidden = True End If Next End Sub Sub findcancel() Rows(“4:195”).EntireRow.Hidden = False End Sub Sub Hide() ‘ ‘ Hide Macro ‘ 宏由 ELooNG 录制，时间: 2009-2-21 ‘ ‘ Range(“AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ”).Select Selection.EntireColumn.Hidden = True Range(“aj239”).Select End Sub Sub View() Columns(“AJ:BA”).Select Selection.EntireColumn.Hidden = False Range(“aj239”).Select End Sub Post Views: 650 地產電腦網絡
