将多个If语句公式转换为VBA [英] Turning a multiple If statement formula into VBA
问题描述
所以我试图在VBA中编写代码，但是我不断得到一个运行时错误'91'。我很困惑，需要调整才能使其运作。
感谢您的帮助
当前公式
= IF（B7 = $ H $ 5，1，IF（B7 = $ H $ 6，.75 （B7 = $ H $ 7中， 75，IF（B7 = $ H $ 8中， 1，IF（B7 = $ H $ 9中， 1，IF（B7 = $ H $ 10中， 1，IF（ B7 = $ H $ 11 5，IF（B7 = $ H $ 12 5，IF（B7 = $ H $ 13 5，IF（B7 = $ H $ 14 5， IF（B7 = $ H $ 15， 1，IF（B7 = $ H $ 16 75，IF（B7 = $ H $ 17为 1，IF（B7 = $ H $ 18日， 1，IF （B7 = $ H $ 19日， 75，IF（B7 = $ H $ 20中， 1，IF（B7 = $ H $ 21为 1，IF（B7 = $ H $ 22为 1，IF（ B7 = $ H $ 23为 1，IF（B7 = $ H $ 24 75，IF（B7 = $ H $ 25为 1，IF（B7 = $ H $ 26 75，IF（ B7 = $ H $ 27 5，IF（B7 = $ H $ 28为 1，IF（B7 = $ H $ 29日， 75，IF（B7 = $ H $ 30 5，IF （B7 = $ H $ 31为 1，IF（B7 = $ H $ 32为 1，IF（B7 = $ H $ 33为 1，IF（B7 = $ H $ 34 5，IF（ B7 = $ H $ 35为 1，IF（B7 = $ H 36 $， 25，IF（B7 = $ H $ 37为 1，IF（B7 = $ H $ 38为 1，IF（B7 = $ H 39 $， 1，IF（B7 = $ H $ 40 1，IF（B7 = $ H $ 41为 1，IF（B7 = $ H $ 42为 1，IF（B7 = $ H $ 43为 1，IF（B7 = $ H $ 44为 1，IF（B7 = $ H $ 45为 1，IF（B7 = $ H $ 46为 1，IF（B7 = $ H $ 47 ， 1，IF（B7 = $ H $ 48为 1，IF（B7 = $ H $ 49为 1，IF（B7 = $ H $ 50 5，IF（B7 = $ H $ 51 1，IF（B7 = $ H $ 52 25，IF（B7 = $ H $ 53为 1，IF（B7 = $ H $ 54 75，IF（B7 = $ H $ 55 1， IF（B7 = $ H $ 56为 1，IF（B7 = $ H $ 57 1））））））））））））））））））））））））））） ）））））））））））））））））））））））/ / code>
我的尝试将其转换成VBA代码
Sub Macro（）
Dim Whole as long
Dim Third as long
一半长一点
二分之一长
Dim lookat as range
Dim answer as range
Whole = 1
third =。 75
Half = .5
Quarter = .25
Lookat = Worksheets（sheet1.Range（B2：B300）
答案=工作表（如果Lookat =AAAA或AAAB或AAAC或AAAD或AAAE或AAAF或AAAG，则范围（C2：C300）
或AAAH或AAAI或AAAJ或AAAK或AAAL或AAAM或AAAN或AAAO或AAAP或AAAQ或AAAR或AAAS或 AAAT或AAAU或AAAV_
或AAAW或AAAX或AAAY或AAAZ或BBBA或BBBB或BBBC或BBBD或BBBE 或BBBF或BBBG然后
Answer.value =全
ElseIf Lookat =AAA或AAB或AAC或AAD或AAE或AAF或AAG或AAH然后
An swer.Value = Third
ElseIf Lookat =AA或AB或AC或AD或AE或AF或AG或AH然后
答案。 Value = Half
ElseIf Lookat =A或Bthen
Answer.Value = Quarter
end if
End Sub
1保护您的工作簿链接或将其隐藏在工作表中
2创建您的数据库（您的H列）
3在我栏中放置您的体重[整体，三分之一，一半，四分之一]。示例：
+ --------- + -------- +
| COL'H'| HEIGHT |
+ --------- + -------- +
| 6 | 1 |
| 9 | 0,5 |
| 4 | 0,75 |
| 6 | 1 |
| 8 | 0,5 |
| 1 | 0,75 |
| 5 | 1 |
| 4 | 0,5 |
| 5 | 0,75 |
| 7 | 1 |
| 4 | 0,5 |
| 9 | 0,75 |
| 1 | 1 |
| 8 | 0,5 |
| 1 | 0,75 |
| 5 | 1 |
| 1 | 0,5 |
| 4 | 0,75 |
| 4 | 1 |
| 1 | 0,5 |
| 7 | 0,75 |
+ --------- + -------- +
4将您当前的公式更改为：
= VLOOKUP（B8，H：I，2，FALSE）
PS：您可以尝试命名您的数据库，使您的代码更好地了解链接。
I currently have the following formula inside a column of cells but as you can see its large and messy and I am afraid that the cells might get edited by someone working in the workbook accidentally.
So I have tried to code it in VBA but I keep getting a Run-Time Error '91'. And I am stuck as to what I need to adjust to make it operate.
thanks for your help
Current Formula
=IF(B7=$H$5,"1",IF(B7=$H$6,".75",IF(B7=$H$7,".75",IF(B7=$H$8,"1",IF(B7=$H$9,"1",IF(B7=$H$10,"1",IF(B7=$H$11,".5",IF(B7=$H$12,".5",IF(B7=$H$13,".5",IF(B7=$H$14,".5",IF(B7=$H$15,"1",IF(B7=$H$16,".75",IF(B7=$H$17,"1",IF(B7=$H$18,"1",IF(B7=$H$19,".75",IF(B7=$H$20,"1",IF(B7=$H$21,"1",IF(B7=$H$22,"1",IF(B7=$H$23,"1",IF(B7=$H$24,".75",IF(B7=$H$25,"1",IF(B7=$H$26,".75",IF(B7=$H$27,".5",IF(B7=$H$28,"1",IF(B7=$H$29,".75",IF(B7=$H$30,".5",IF(B7=$H$31,"1",IF(B7=$H$32,"1",IF(B7=$H$33,"1",IF(B7=$H$34,".5",IF(B7=$H$35,"1",IF(B7=$H$36,".25",IF(B7=$H$37,"1",IF(B7=$H$38,"1",IF(B7=$H$39,"1",IF(B7=$H$40,"1",IF(B7=$H$41,"1",IF(B7=$H$42,"1",IF(B7=$H$43,"1",IF(B7=$H$44,"1",IF(B7=$H$45,"1",IF(B7=$H$46,"1",IF(B7=$H$47,"1",IF(B7=$H$48,"1",IF(B7=$H$49,"1",IF(B7=$H$50,".5",IF(B7=$H$51,"1",IF(B7=$H$52,".25",IF(B7=$H$53,"1",IF(B7=$H$54,".75",IF(B7=$H$55,"1",IF(B7=$H$56,"1",IF(B7=$H$57,"1")))))))))))))))))))))))))))))))))))))))))))))))))))))
My attempt at turning it into VBA code
Sub Macro()
Dim Whole as long
Dim Third as long
Dim half as long
Dim quarter as long
Dim lookat as range
Dim answer as range
Whole = 1
third = .75
Half = .5
Quarter = .25
Lookat = Worksheets("sheet1".Range("B2:B300")
Answer = worksheets("Sheet1").range("C2:C300")
If Lookat = "AAAA" Or "AAAB" Or "AAAC" Or "AAAD" Or "AAAE" Or "AAAF" Or "AAAG" Or "AAAH" Or "AAAI" Or "AAAJ" Or "AAAK" Or "AAAL" Or "AAAM" Or "AAAN" Or "AAAO" Or "AAAP" Or "AAAQ" Or "AAAR" Or "AAAS" Or "AAAT" Or "AAAU" Or "AAAV" _
Or "AAAW" Or "AAAX" Or "AAAY" Or "AAAZ" Or "BBBA" Or "BBBB" Or "BBBC" Or "BBBD" Or "BBBE" Or "BBBF" Or "BBBG" Then
Answer.value=whole
ElseIf Lookat = "AAA" Or "AAB" Or "AAC" Or "AAD" Or "AAE" Or "AAF" Or "AAG" Or "AAH" Then
Answer.Value = Third
ElseIf Lookat = "AA" Or "AB" Or "AC" Or "AD" Or "AE" Or "AF" Or "AG" Or "AH" Then
Answer.Value = Half
ElseIf Lookat = "A" Or "B" Then
Answer.Value = Quarter
end if
End Sub
1 Protect your Workbook Link or hide it in a worksheet
2 Create your DataBase (your 'H' column)
3 In 'I' Column put your Weight [whole, third, half, quarter]. Sample:
+---------+--------+
| COL 'H' | HEIGHT |
+---------+--------+
| 6 | 1 |
| 9 | 0,5 |
| 4 | 0,75 |
| 6 | 1 |
| 8 | 0,5 |
| 1 | 0,75 |
| 5 | 1 |
| 4 | 0,5 |
| 5 | 0,75 |
| 7 | 1 |
| 4 | 0,5 |
| 9 | 0,75 |
| 1 | 1 |
| 8 | 0,5 |
| 1 | 0,75 |
| 5 | 1 |
| 1 | 0,5 |
| 4 | 0,75 |
| 4 | 1 |
| 1 | 0,5 |
| 7 | 0,75 |
+---------+--------+
4 Change your current formula to:
=VLOOKUP(B8,H:I,2,FALSE)
PS: You can try to name your Database to make your code better to understand Link.
这篇关于将多个If语句公式转换为VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
