Unable to set the FormulaArray property of the Range class
Posted: Thu Feb 26, 2015 3:38 pm
เรียนอาจารย์และเพื่อนสมาชิกทุกท่านครับvba ขึ้น Run-time error '1004':
Unable to set the FormulaArray property of the Range class
Code: Select all
Sub sheet1()
Dim ws As Worksheet
Dim r As Integer
Set ws = ActiveWorkbook.Worksheets("sheet1")
With ws
For r = 2 To 10
Cells(r, 41).FormulaArray = _
"=IF(OR(R[0]C[+1] = ""no"",COUNTIF(R2C36:R[0]C[-5],R[0]C[-5])=1,MAX(IF(R2C[-5]:R[0]C[-5]=R[0]C[-5],IF(R2C[+1]:R[0]C[+1]<>""no"",R2C[-3]:R[0]C[-3])))=0,MAX(IF((R1C[-5]:R[-1]C[-5]=R[0]C[-5],IF(R1C[+1]:R[-1]C[+1]<>""no"",R1C[-2]:R[-1]C[-2])))=0),0,MAX(IF(R2C[-5]:R[0]C[-5]=R[0]C[-5],IF(R2C[+1]:R[0]C[+1]<>""no"",R2C[-3]:R[0]C[-3])))-MAX(IF(R1C[-5]:R[-1]C[-5]=R[0]C[-5],IF(R1C[+1]:R[-1]C[+1]<>""no"",R1C[-2]:R[-1]C[-2]))))"
Next r
End With
End SubCode: Select all
Cells(r, 41).FormulaArray = _
"=IF(OR(R[0]C[+1] = ""no"",COUNTIF(R2C36:R[0]C[-5],R[0]C[-5])=1,MAX(IF(R2C[-5]:R[0]C[-5]=R[0]C[-5],IF(R2C[+1]:R[0]C[+1]<>""no"",R2C[-3]:R[0]C[-3])))=0,MAX(IF((R1C[-5]:R[-1]C[-5]=R[0]C[-5],IF(R1C[+1]:R[-1]C[+1]<>""no"",R1C[-2]:R[-1]C[-2])))=0),0,MAX(IF(R2C[-5]:R[0]C[-5]=R[0]C[-5],IF(R2C[+1]:R[0]C[+1]<>""no"",R2C[-3]:R[0]C[-3])))-MAX(IF(R1C[-5]:R[-1]C[-5]=R[0]C[-5],IF(R1C[+1]:R[-1]C[+1]<>""no"",R1C[-2]:R[-1]C[-2]))))"
ให้ด้วยครับขอบคุณครับ
ผมเขียนถูกแล้วแต่มีข้อจำกัดนี่เอง ขอบคุณครับอาจารย์ 
