[算表] 無法取得worksheetfunction的MATCH屬性

[jimmy0413 (小M&小I)]

軟體: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://www.ptt.cc/bbs/Office/M.1458755550.A.FD3.html
→ soyoso: 是否vatoprange1或vatoprange2的值於pricerow array內是找
→ soyoso: 不到呢？https://imgur.com/4c62fVo 測試如有值時是可以執
→ soyoso: 行的
→ soyoso: 當找不到值時則出現如原文的錯誤
→ soyoso: https://imgur.com/TOgHztL
→ soyoso: 另外dim宣告可不寫於迴圈內；如要將vatoprange1、
→ soyoso: vatoprange2,vabottomrange1,vabottomrang2宣告為integer
→ soyoso: 時，寫法需個別指定
→ soyoso: tponumber無看到累加，會有無窮迴圈的可能
→ soyoso:                ^^^^累加減