Turning a multiple If statement formula into VBA

问题:

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:

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.

回答2:

Something like this may suit:

+----+-----+------+
|    |  A  |  B   |
+----+-----+------+
|  1 | H10 | 1    |
|  2 | H14 | 0.5  |
|  3 | H15 | 1    |
|  4 | H16 | 0.75 |
|  5 | H18 | 1    |
|  6 | H19 | 0.75 |
|  7 | H23 | 1    |
|  8 | H24 | 0.75 |
|  9 | H25 | 1    |
| 10 | H26 | 0.75 |
| 11 | H27 | 0.5  |
| 12 | H28 | 1    |
| 13 | H29 | 0.75 |
| 14 | H30 | 0.5  |
| 15 | H33 | 1    |
| 16 | H34 | 0.5  |
| 17 | H35 | 1    |
| 18 | H36 | 0.25 |
| 19 | H49 | 1    |
| 20 | H5  | 1    |
| 21 | H50 | 0.5  |
| 22 | H51 | 1    |
| 23 | H52 | 0.25 |
| 24 | H53 | 1    |
| 25 | H54 | 0.75 |
| 26 | H57 | 1    |
| 27 | H7  | 0.75 |
+----+-----+------+

located in a sheet on its own in the same workbook and the array named say Vt of Workbook Scope.

This might then be used as the lookup table with a formula such as:

=VLOOKUP(A1,Vt,2)  

where A1 contains a value such as "H10" for the purposes of this example.

It is important to sort A:B of the 'other' (to-be-hidden/protected) sheet in ascending order of ColumnA if to take advantage of a table that does not specifically equate every possible value. The lack of a fourth parameter in the VLOOKUP formula (though often causing problems!) means that an approximate match will be found where there is no exact match.