每行迭代中的逻辑运算 [英] Logical Operations in a For Each Row Iteration
问题描述
代码的目标是：
- 阅读单元格F中的可用性代码 - 可以从300-799中的任何 nnn
- 确定其中的范围下降 - 300-499,500-599,600-699,700-799，除了762,763,764,765,768
- 在单元格I中分配容量 - 当前使用
Row.Offset -
重复下一行并重复到最后一个F单元格，数据
Sub Capacity（）
Dim ACNum As Range
Dim WB As Workbook
Dim WS作为工作表
设置WB = ActiveWorkbook
设置WS = WB.ActiveWorksheet
设置ACNum = WS.Range（F：F）
对于每行ACNum.Cells
设置AC = WS.Cells（WS.Rows.Count，F）。End（xlUp）.Offset（1）
如果（AC =762或AC =763或AC =764或AC =765或AC =768）然后
Row.Offset（0,3）.Value =72
ElseIf（AC> ; =300，AC <=499） n
Row.Offset（0，3）.Value =181
ElseIf（AC> =500而AC <=599）然后
Row.Offset 0，3）.Value =163
ElseIf（AC> =600而AC <=699）然后
Row.Offset（0，3）.Value =124
ElseIf（AC> =700而AC <=799）和_
（AC& 762或AC 763或AC 764或AC 765或AC 768）然后
Row.Offset（0，3）.Value =144
如果
下一行
End Sub
每次我执行这个我收到一个通用错误，以黄色突出显示，第13行，第一个如果语句的开头。如果我试图使用Excel的评估工具，它的选项是灰色的，我无法通过宏。
我无法确定我在做什么错误。我没有在Excel VBA工作一段时间，所以我可能会缺少一个小细节。
编辑：添加了一个公式 p>
这应该是
Option Explicit
Sub Capacity（）
Dim cell As Range
Dim noNumbers As String
Dim code As Integer
noNumbers =-762-763-764- 765-768-'< == code exceptions
对于每个单元格在ActiveSheet.Columns（F）。SpecialCells（xlCellTypeConstants，xlNumbers）'< =只考虑可用性代码列
单元格
如果InStr（noNumbers， - & .value& - ）> 0然后
code = 72
Else
选择案例.value
案例300到499
代码= 181
案例500到599
代码= 163
案例600到699
代码= 124
案例700到799
代码= 144
案例Else
代码= -1
结束选择
结束如果
.Offset（0，3）=代码
结束
下一个单元格
End Sub
根据您的解释，我假设可用性代码实际上是一个数字，所以更好地声明和使用变量（ code ）来处理它，然后使用选择案例语句去掉解析标准，避免重复很多人物
作为Dan Donoghue提出你可能想使用一个公式proach。我来到以下，使用中位数功能，从 http ：//chandoo.org/wp/2010/06/24/between-formula-excel/
子容量2（）
使用ActiveSheet.Columns（F）。SpecialCells（xlCellTypeConstants，xlNumbers）'< =仅考虑可用性代码列中的数字
.Offset（，3） .FormulaR1C1 == if（isnumber（search（ - & RC [-3]& - ， - 762-763-764-765-768 - ）），72如果（RC [-3] = MEDIAN（RC [-3]，300,499），181，_
&if（RC [-3] = MEDIAN RC [-3]，500,599），163，_
&如果（RC [-3] = MEDIAN（RC [-3]，600,699），124，_
&如果（RC [-3] = MEDIAN（RC [-3]，700,799），144，-1_
&））））））
End With
当然，你也可以实现直接在excel单元格中，而不是运行VBA宏
I'm updating a table of capacities which are based on an availability code. Availability codes in specific ranges have the same capacity (with a few exceptions). Each availability code is listed in column "F". The associated capacity is to be listed in column "I".
The goal of the code is to:
- Read the availability code in cell "F" - can be any nnn from 300-799
- Determine the range in which it falls - 300-499, 500-599, 600-699, 700-799, with exceptions 762, 763, 764, 765, 768
- Assign a capacity in cell "I" - currently using
Row.Offset Iterate down one row and repeat to the last "F" cell with data
Sub Capacity() Dim ACNum As Range Dim WB As Workbook Dim WS As Worksheet Set WB = ActiveWorkbook Set WS = WB.ActiveWorksheet Set ACNum = WS.Range("F:F") For Each Row In ACNum.Cells Set AC = WS.Cells(WS.Rows.Count, "F").End(xlUp).Offset(1) If (AC = "762" Or AC = "763" Or AC = "764" Or AC = "765" Or AC = "768") Then Row.Offset(0, 3).Value = "72" ElseIf (AC >= "300" And AC <= "499") Then Row.Offset(0, 3).Value = "181" ElseIf (AC >= "500" And AC <= "599") Then Row.Offset(0, 3).Value = "163" ElseIf (AC >= "600" And AC <= "699") Then Row.Offset(0, 3).Value = "124" ElseIf (AC >= "700" And AC <= "799") And _ (AC <> "762" Or AC <> "763" Or AC <> "764" Or AC <> "765" Or AC <> "768") Then Row.Offset(0, 3).Value = "144" End If Next Row End Sub
Each time I execute this I receive a generic error, highlighted in yellow, at Line 13, the start of the first If statement. If I attempt to use Excel's Evaluate tool, it's options are greyed out and I can't step through the macro.
I'm unable to determine what I'm doing wrong. I haven't worked in Excel VBA for some time so I may be missing a minor detail. This seems so straight-forward.
edited: added a "formula" approach solution
this should do
Option Explicit
Sub Capacity()
Dim cell As Range
Dim noNumbers As String
Dim code As Integer
noNumbers = "-762-763-764-765-768-" '<== code exceptions
For Each cell In ActiveSheet.Columns("F").SpecialCells(xlCellTypeConstants, xlNumbers) '<= consider only numbers in "availibility code" column
With cell
If InStr(noNumbers, "-" & .value & "-") > 0 Then
code = 72
Else
Select Case .value
Case 300 To 499
code = 181
Case 500 To 599
code = 163
Case 600 To 699
code = 124
Case 700 To 799
code = 144
Case Else
code = -1
End Select
End If
.Offset(0, 3) = code
End With
Next cell
End Sub
as per your explanation I assumed that "availability code" is actually a number so it's much better to declare and use a variable (code) of type "integer" to handle it and then use Select Case statement to go away with parsing criteria avoiding the repetition of a lot of characters
as Dan Donoghue proposed you may want to use a formula approach. I came up to the following, using the "Median" function as from http://chandoo.org/wp/2010/06/24/between-formula-excel/
Sub Capacity2()
With ActiveSheet.Columns("F").SpecialCells(xlCellTypeConstants, xlNumbers) '<= consider only numbers in "availibility code" column
.Offset(, 3).FormulaR1C1 = "=if(isnumber(search(""-"" & RC[-3] & ""-"",""-762-763-764-765-768-"")),72," _
& "if(RC[-3]=MEDIAN(RC[-3],300,499),181," _
& "if(RC[-3]=MEDIAN(RC[-3],500,599),163," _
& "if(RC[-3]=MEDIAN(RC[-3],600,699),124," _
& "if(RC[-3]=MEDIAN(RC[-3],700,799),144,-1" _
& ")))))"
End With
which, of course, you may also implement directly in excel cells instead of having a VBA macro running
这篇关于每行迭代中的逻辑运算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
