I want to check for each item in column A (Position) whether one of the values A to G is assigned to column AT (Size), otherwise it give an error.(The number of articles isn't constant). this code does not work:

With ThisWorkbook.Worksheets("Ordersheet")

Row = 10

Do Until ThisWorkbook.Worksheets("Ordersheet").Cells(zeile, 1).Value <> ""

 If Sheets(ActiveSheet).Cells(Row, 46).Value = "A" Or Sheets(ActiveSheet).Cells(Row, 46).Value = "B" Or Sheets(ActiveSheet).Cells(Row, 46).Value = "C" Or Sheets(ActiveSheet).Cells(Row, 46).Value = "D" Or Sheets(ActiveSheet).Cells(Row, 46).Value = "E" Or Sheets(ActiveSheet).Cells(Row, 46).Value = "F" Or Sheets(ActiveSheet).Cells(Row, 46).Value = "G" Then
  Row = Row   1
   Exit Do
    Else
    MsgBox ("fill Größenlauf ")
    End If
Loop
End With

CodePudding user response：

Select Case is useful when checking for one of multiple values.

Try this:

Dim rw As Long

rw = 10
With ThisWorkbook.Worksheets("Ordersheet")
    Do While Len(.Cells(rw, "A").Value) > 0
        Select Case .Cells(rw, "AT").Value
            Case "A", "B", "C", "D", "E", "F", "G"
                'OK
            Case Else
                MsgBox "Fill Größenlauf on row# " & rw
                Exit Do
        End Select
        rw = rw   1
    Loop
End With

CodePudding user response：

As far as I understand your post you want to

• check the validity of character inputs (starting in cell AT11)
• by giving an error notice if the cell values don't equal characters "A" to "G" (which of course excludes empty cells or other values).

Some hints to frequent issues

• Looping through a range by means of VBA can be time consuming, it's faster to use arrays (see section b and c).
• It's faster to calculate the last row (c.f. section a) than to check each row separately via Do Until ThisWorkbook.Worksheets("Ordersheet").Cells(zeile, 1).Value <> "", where btw you forgot to change zeile to the variable name Row. - Note that it's no good coding practice to shadow existing property names like Row with an identical variable name (so you could use r instead for instance).
• Instead of checking all character occurrencies separately, I demonstrate a use via the Like operator (c.f. section d) via If Not data(i, 1) Like "[ABCDEFG]" Then. ~~> Of course you might use a Select Case structure, too as shown some minutes before by @TimWilliams.
• The logic in your original code example stops the row loop with the first finding of a invalid or empty character wheras I demonstrate how to get all found cells.
• All notices get assigned to the 1-dim array missing, get redimmed to the actual count of invalid cells and shown by joining all findings.
• Furthermore Sheets(ActiveSheet) is no valid code, think you wanted say Sheets("Ordersheet") or even better ThisWorkbook.Worksheets("Ordersheet")in order to fully qualify your sheet or range reference. If you actually want to use the currently active sheet it suffices to use simplyActiveSheet`.

Option Explicit                ' force variable declarations in code module head

Sub checkValidity()
Const StartRow As Long = 2     ' define fixed start row of data to be checked
With ThisWorkbook.Worksheets("Ordersheet")
'a) get last row
    Dim lastRow As Long
    lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
'b) get 1-based 2-dimensional datafield array
    Dim data As Variant
    data = .Range("AT1:AT" & lastRow)
End With
'c) provide for sufficient elements in a "flat" 1-dim array named missing
    Dim missing() As String
    ReDim missing(1 To UBound(data))
'd) show invalid data in VB Editor's immediate window

    Dim i As Long, ii As Long
    For i = StartRow To UBound(data)            ' start check at row 11!
        If Not data(i, 1) Like "[ABCDEFG]" Then ' don't accept characters other than A..G
            ii = ii   1                         ' increment counter for missing elements
            missing(ii) = "AT" & i              ' remember cell address, e.g. AT11
        End If
    Next
    ReDim Preserve missing(1 To ii)             ' resize to actually found elements
    
    Debug.Print "Missing entries in the following cells:" & vbNewLine & _
                Join(missing, ", ")
    'alternatively:
    'MsgBox "Missing entries in cells" & vbNewLine & Join(missing, ", "), vbExclamation, "Invalid entries"
End Sub