作者jimmy0413 (小M&小I)
看板Office
標題[算表] 無法取得worksheetfunction的MATCH屬性
時間Thu Mar 24 01:52:25 2016
軟體:EXCEL
VBA
版本:2010
這是我的VBA程式碼，不知道為何在
"sumup = Application.WorksheetFunction.Index(tponumberrow, 1,
Application.WorksheetFunction.Match(vatoprange1, pricerow, 0)) +
Application.WorksheetFunction.Index(tponumberrow, 1,
Application.WorksheetFunction.Match(vatoprange2, pricerow, 0))"
一直出現出現錯誤"無法取得worksheetfunction的MATCH屬性"
試了超級九還是不知道是哪裡出了問題，有人可以幫我看一下嗎><
以下是我的程式碼
Sub 算VA()
Dim tponumberrow, abc As Range
Dim pricerow, bcd As Range
Dim varange()
Dim tponumber As Integer
Dim sumup As Integer
Dim sumdown As Integer
tponumberrow = Worksheets("運算").Range("c3:ST3")
pricerow = Worksheets("運算").Range("c4:ST4")
varange = Array(Worksheets("運算").Range("d2").Value)
Do While tponumber <= Worksheets("運算").Range("J2").Value * 0.7
Dim maxvarange As Integer
Dim minvarange As Integer
maxvarange = varange(LBound(varange))
minvarange = varange(LBound(varange))
For i = LBound(varange) + 1 To UBound(varange)
If maxvarange < varange(i) Then maxvarange = varange(i)
Next i
For i = LBound(varange) + 1 To UBound(varange)
If minvarange > varange(i) Then minvarange = varange(i)
Next i
Dim vatoprange1, vatoprange2, vabottomrange1, vabottomrange2 As Integer
vatoprange1 = maxvarange + 2
vatoprange2 = maxvarange + 1
vabottomrange1 = minvarange - 2
vabottomrange2 = minvarange - 1
sumup = Application.WorksheetFunction.Index(tponumberrow, 1,
Application.WorksheetFunction.Match(vatoprange1, pricerow, 0)) +
Application.WorksheetFunction.Index(tponumberrow, 1,
Application.WorksheetFunction.Match(vatoprange2, pricerow, 0))
sumdown = Application.WorksheetFunction.Index(tponumberrow, 1,
Application.WorksheetFunction.Match(vabottomrange1, pricerow, 0)) +
Application.WorksheetFunction.Index(tponumberrow, 1,
Application.WorksheetFunction.Match(vabottomrange2, pricerow, 0))
For i = LBound(varange) + 1 To UBound(varange)
If maxvarange < varange(i) Then maxvarange = varange(i)
Next i
For i = LBound(varange) + 1 To UBound(varange)
If minvarange > varange(i) Then minvarange = varange(i)
Next i
If sumup > sumdown Then
varange(LBound(varange) + 1) = maxvarange + 1
varange(LBound(varange) + 2) = maxvarange + 2
End If
Loop
MsgBox (maxvarange)
End Sub
非常不好意思我是初學者，也沒有人可以詢問，所以只能上來這邊問了，
有人能夠幫忙的話我會非常感激的QQ
--
自Q八>.^
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 114.36.66.94
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Office/M.1458755550.A.FD3.html
1F：→ soyoso: 是否vatoprange1或vatoprange2的值於pricerow array內是找 03/24 08:01
3F：→ soyoso: 行的 03/24 08:02
4F：→ soyoso: 當找不到值時則出現如原文的錯誤 03/24 08:05
6F：→ soyoso: 另外dim宣告可不寫於迴圈內；如要將vatoprange1、 03/24 08:07
7F：→ soyoso: vatoprange2,vabottomrange1,vabottomrang2宣告為integer 03/24 08:08
8F：→ soyoso: 時，寫法需個別指定 03/24 08:09
9F：→ soyoso: tponumber無看到累加，會有無窮迴圈的可能 03/24 08:09
10F：→ soyoso: ^^^^累加減 03/24 08:11