[已解决][求助] 请问如何取列的相对值

ragnarok

第一次用VBA，做了一个表，想根据J、K、O、P、Q、R、S、T、U、V列的值求W列的值
现在的问题是：我函数已经编好了，但是因为取值时取得是绝对地址（比如：Jt = Range("J3").Value
），虽然W3的计算结果是正确的，但是不能像不用VBA时一样，只要往下一拉，W4、W5、W6.........的结果就自动出来
求取相对地址的办法，我把表的附件打包上传了，请帮忙看一下

1. Sub CottonMoney()
   
2. Dim Jt As Integer   
   
3. Dim Ju As Integer
   
4. Dim Jv As Integer
   
5. Dim Pprice As Integer   
   
6. Dim Qprice As Integer
   
7. Dim Rprice As Integer
   
8. Dim Sprice As Integer
   
9. Dim Ppercent As Single  
   
10. Dim Qpercent As Single
    
11. Dim Rpercent As Single
    
12. Dim Spercent As Single
    
13. Dim Weight As Single   
    
14. Dim Money As Single
    
15. 
    
16. Select Case Range("T3").Value   
    
17.     Case 27 To 29
    
18.         Jt = Range("J3").Value
    
19.     Case 30 To 31
    
20.         Jt = Range("J3").Value + 100
    
21. End Select
    
22. 
    
23. Select Case Range("U3").Value   
    
24.     Case "A"
    
25.         Ju = Jt + 50
    
26.     Case "B"
    
27.         Ju = Jt
    
28.     Case "C"
    
29.         Ju = Jt - 100
    
30. End Select
    
31. 
    
32. If Range("V3").Value < 2 Then   
    
33.     Jv = Ju
    
34. Else
    
35.     Jv = Jv - 200 * (Range("V3").Value - 1)
    
36. End If
    
37. 
    
38. If WorksheetFunction.Max(Range("P3").Value, Range("Q3").Value, Range("R3").Value, Range("S3").Value) >= 0.8 Then
    
39.     Ppercent = Range("P3").Value
    
40.     Qpercent = Range("Q3").Value
    
41.     Rpercent = Range("R3").Value
    
42.     Spercent = Range("S3").Value
    
43. ElseIf Range("P3").Value + Range("Q3").Value >= 0.8 Then
    
44.     Ppercent = 0
    
45.     Qpercent = Range("Q3").Value + Range("P3").Value
    
46.     Rpercent = Range("R3").Value
    
47.     Range("S3").Value = Range("S3").Value
    
48. ElseIf Range("Q3").Value + Range("R3").Value >= 0.8 Then
    
49.     Ppercent = 0
    
50.     Qpercent = 0
    
51.     Rpercent = Range("R3").Value + Range("P3").Value + Range("Q3").Value
    
52.     Spercent = Range("S3").Value
    
53. Else
    
54.     Ppercent = 0
    
55.     Qpercent = 0
    
56.     Rpercent = 0
    
57.     Spercent = Range("S3").Value + Range("P3").Value + Range("Q3").Value + Range("R3").Value
    
58. End If
    
59. 
    
60. If Range("K3").Value = "三级" Then  
    
61.     Pprice = Jv + 300
    
62.     Qprice = Jv
    
63.     Rprice = Jv - 500
    
64.     Sprice = Jv - 1300
    
65. Else
    
66.     Pprice = Jv + 800
    
67.     Qprice = Jv + 500
    
68.     Rprice = Jv
    
69.     Sprice = Jv - 800
    
70. End If
    
71. 
    
72. Weight = Range("O3").Value
    
73. 
    
74. Money = Weight * (Pprice * Ppercent + Qprice * Qpercent + Rprice * Rpercent + Sprice * Spercent)   
    
75. 
    
76. Range("W3").Value = Money
    
77. 
    
78. End Sub
    

复制代码

情况一览表(修改).rar (9.9 KB, 下载次数: 6)

2012-3-13 11:46 上传

点击文件名下载附件

最佳答案

月排行榜 / 总排行榜

happym8888

2012-3-13 12:44

情况一览表(修改).rar (12.88 KB, 下载次数: 8)

2012-3-13 12:44 上传

点击文件名下载附件

1. Public Function FN(J, K, O, P, Q, R, S, T, U, V) As Currency
   
2. Dim Jt As Integer '定义采购价变量
   
3. Dim Ju As Integer
   
4. Dim Jv As Integer
   
5. Dim Pprice As Integer '定义各等级棉花根据采购价换算出来的价格变量
   
6. Dim Qprice As Integer
   
7. Dim Rprice As Integer
   
8. Dim Sprice As Integer
   
9. Dim Ppercent As Single '定义各等级棉花百分比变量
   
10. Dim Qpercent As Single
    
11. Dim Rpercent As Single
    
12. Dim Spercent As Single
    
13. Dim Weight As Single '定义公检重量变量
    
14. Dim Money As Single '定义预算货款金额变量
    
15. 
    
16. Select Case T '长度升水
    
17. Case 27 To 29
    
18. Jt = J
    
19. Case 30 To 31
    
20. Jt = J + 100
    
21. End Select
    
22. 
    
23. Select Case U '马克隆值升贴水
    
24. Case "A"
    
25. Ju = Jt + 50
    
26. Case "B"
    
27. Ju = Jt
    
28. Case "C"
    
29. Ju = Jt - 100
    
30. End Select
    
31. 
    
32. If V < 2 Then '异纤贴水
    
33. Jv = Ju
    
34. Else
    
35. Jv = Jv - 200 * (V - 1)
    
36. End If
    
37. 
    
38. If WorksheetFunction.Max(P, Q, R, S) >= 0.8 Then '判断主体品级
    
39. Ppercent = P
    
40. Qpercent = Q
    
41. Rpercent = R
    
42. Spercent = S
    
43. ElseIf P + Q >= 0.8 Then
    
44. Ppercent = 0
    
45. Qpercent = Q + P
    
46. Rpercent = R
    
47. S = S
    
48. ElseIf Q + R >= 0.8 Then
    
49. Ppercent = 0
    
50. Qpercent = 0
    
51. Rpercent = R + P + Q
    
52. Spercent = S
    
53. Else
    
54. Ppercent = 0
    
55. Qpercent = 0
    
56. Rpercent = 0
    
57. Spercent = S + P + Q + R
    
58. End If
    
59. 
    
60. If K = "三级" Then '根据采购价换算出各等级棉花的价格
    
61. Pprice = Jv + 300
    
62. Qprice = Jv
    
63. Rprice = Jv - 500
    
64. Sprice = Jv - 1300
    
65. Else
    
66. Pprice = Jv + 800
    
67. Qprice = Jv + 500
    
68. Rprice = Jv
    
69. Sprice = Jv - 800
    
70. End If
    
71. Weight = O
    
72. FN = Weight * (Pprice * Ppercent + Qprice * Qpercent + Rprice * Rpercent + Sprice * Spercent) '分级计价
    
73. End Function
    
74. 
    
75. 
    

复制代码

跳转到最佳答案楼层

zjcat35

你最好说下你W3的计算规则吧!!!!!

ragnarok

zjcat35 发表于 2012-3-13 12:10
你最好说下你W3的计算规则吧!!!!!

W3的计算就在代码里啊，关键是我取得值是绝对值

zjcat35

代码那么长,懒的看,看起来太累了!  

happym8888

情况一览表(修改).rar (12.88 KB, 下载次数: 8)

2012-3-13 12:44 上传

点击文件名下载附件

1. Public Function FN(J, K, O, P, Q, R, S, T, U, V) As Currency
   
2. Dim Jt As Integer '定义采购价变量
   
3. Dim Ju As Integer
   
4. Dim Jv As Integer
   
5. Dim Pprice As Integer '定义各等级棉花根据采购价换算出来的价格变量
   
6. Dim Qprice As Integer
   
7. Dim Rprice As Integer
   
8. Dim Sprice As Integer
   
9. Dim Ppercent As Single '定义各等级棉花百分比变量
   
10. Dim Qpercent As Single
    
11. Dim Rpercent As Single
    
12. Dim Spercent As Single
    
13. Dim Weight As Single '定义公检重量变量
    
14. Dim Money As Single '定义预算货款金额变量
    
15. 
    
16. Select Case T '长度升水
    
17. Case 27 To 29
    
18. Jt = J
    
19. Case 30 To 31
    
20. Jt = J + 100
    
21. End Select
    
22. 
    
23. Select Case U '马克隆值升贴水
    
24. Case "A"
    
25. Ju = Jt + 50
    
26. Case "B"
    
27. Ju = Jt
    
28. Case "C"
    
29. Ju = Jt - 100
    
30. End Select
    
31. 
    
32. If V < 2 Then '异纤贴水
    
33. Jv = Ju
    
34. Else
    
35. Jv = Jv - 200 * (V - 1)
    
36. End If
    
37. 
    
38. If WorksheetFunction.Max(P, Q, R, S) >= 0.8 Then '判断主体品级
    
39. Ppercent = P
    
40. Qpercent = Q
    
41. Rpercent = R
    
42. Spercent = S
    
43. ElseIf P + Q >= 0.8 Then
    
44. Ppercent = 0
    
45. Qpercent = Q + P
    
46. Rpercent = R
    
47. S = S
    
48. ElseIf Q + R >= 0.8 Then
    
49. Ppercent = 0
    
50. Qpercent = 0
    
51. Rpercent = R + P + Q
    
52. Spercent = S
    
53. Else
    
54. Ppercent = 0
    
55. Qpercent = 0
    
56. Rpercent = 0
    
57. Spercent = S + P + Q + R
    
58. End If
    
59. 
    
60. If K = "三级" Then '根据采购价换算出各等级棉花的价格
    
61. Pprice = Jv + 300
    
62. Qprice = Jv
    
63. Rprice = Jv - 500
    
64. Sprice = Jv - 1300
    
65. Else
    
66. Pprice = Jv + 800
    
67. Qprice = Jv + 500
    
68. Rprice = Jv
    
69. Sprice = Jv - 800
    
70. End If
    
71. Weight = O
    
72. FN = Weight * (Pprice * Ppercent + Qprice * Qpercent + Rprice * Rpercent + Sprice * Spercent) '分级计价
    
73. End Function
    
74. 
    
75. 
    

复制代码

ragnarok

happym8888 发表于 2012-3-13 12:44

真是太谢谢了，但是为什么计算结果不正确了呢，变成负数了

happym8888

ragnarok 发表于 2012-3-13 16:10
真是太谢谢了，但是为什么计算结果不正确了呢，变成负数了

W3计算正确吗？和原表格里的不一样，用你原来的代码算出来和原表格里的也是不一样。

ragnarok

happym8888 发表于 2012-3-13 16:18
W3计算正确吗？和原表格里的不一样，用你原来的代码算出来和原表格里的也是不一样。

哎~~~~~我就是这么粗心，写错了，中间有个Ju写成jv了{:4712:}真的谢谢你{:812:}

happym8888

ragnarok 发表于 2012-3-13 16:25
哎~~~~~我就是这么粗心，写错了，中间有个Ju写成jv了真的谢谢你

粗心大意神7神8可上不了天哦