Logical Operations in a For Each Row Iteration

Question

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.

Answer 1

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