For Each

[Nine Zero]

Hello,

I have the code below running perfectly until the situation reaches one of the condition second to last which is the "8CNT"

if it is met then for some reason if the "9END." is met also, it is not run but if the "8CNT" is not met then the "9END." does run

Set headers = Sheets("DATA").Range("C8:K8")
For Each c In headers
    If c.Value = "0BEG" Then
    c.Value = "Starting Quantity"
    ElseIf c.Value = "3XFI" Then
    c.Value = "Transfer Quantity"
    ElseIf c.Value = "4RTN" Then
    c.Value = "Returned Quantity"
    ElseIf c.Value = "5ADI" Then
    c.Value = "Adjusted Quantity (+)"
    ElseIf c.Value = "2SAL" Then
    c.Value = "Sold Quantity"
    ElseIf c.Value = "3XFO" Then
    c.Value = "Picked Quantity"
    ElseIf c.Value = "5ADO" Then
    c.Value = "Adjusted Quantity (-)"
    ElseIf c.Value = "7FRZ" Then
    c.Value = "Ending Quantity"
    ElseIf c.Value = "2POR" Then
    c.Value = "Purchased Quantity"
[COLOR=#ff0000][B]    ElseIf c.Value = "8CNT" Then[/B][/COLOR]
[COLOR=#ff0000][B]    c.EntireColumn.Delete[/B][/COLOR]
[COLOR=#ff0000][B]    ElseIf c.Value = "9END." Then[/B][/COLOR]
[COLOR=#ff0000][B]    c.EntireColumn.Delete[/B][/COLOR]
    End If
    Next

Is it because im basically hopping or skipping over that c value at that moment that i just deleted??? how can i avoid that... ive seen where it is best to work backwards as to not miss it but i dont know how to set that up

 

[shg]

Sub Nine0()
  Dim iCol          As Long

  With Worksheets("DATA")
    For iCol = 11 To 3 Step -1
      Select Case .Cells(8, iCol).Value
        Case "0BEG": .Cells(8, iCol).Value = "Starting Quantity"
        Case "3XFI": .Cells(8, iCol).Value = "Transfer Quantity"
        Case "4RTN": .Cells(8, iCol).Value = "Returned Quantity"
        Case "5ADI": .Cells(8, iCol).Value = "Adjusted Quantity (+)"
        Case "2SAL": .Cells(8, iCol).Value = "Sold Quantity"
        Case "3XFO": .Cells(8, iCol).Value = "Picked Quantity"
        Case "5ADO": .Cells(8, iCol).Value = "Adjusted Quantity (-)"
        Case "7FRZ": .Cells(8, iCol).Value = "Ending Quantity"
        Case "2POR": .Cells(8, iCol).Value = "Purchased Quantity"
        Case "8CNT", "9END.": .Columns(iCol).Delete
      End Select
    Next iCol
  End With
End Sub

 

[V_Malkoti]

Changes highlighted:

Sub testSub()
    Dim headers As Range
    Dim c As Range
    
    Set headers = Sheets("DATA").Range("C8:K8")
[COLOR=#0000ff]    For i = headers.Columns.Count To headers.Column Step -1[/COLOR]
[COLOR=#0000ff]        Set c = headers(i)[/COLOR]
        If c.Value = "0BEG" Then
            c.Value = "Starting Quantity"
        ElseIf c.Value = "3XFI" Then
            c.Value = "Transfer Quantity"
        ElseIf c.Value = "4RTN" Then
            c.Value = "Returned Quantity"
        ElseIf c.Value = "5ADI" Then
            c.Value = "Adjusted Quantity (+)"
        ElseIf c.Value = "2SAL" Then
            c.Value = "Sold Quantity"
        ElseIf c.Value = "3XFO" Then
            c.Value = "Picked Quantity"
        ElseIf c.Value = "5ADO" Then
            c.Value = "Adjusted Quantity (-)"
        ElseIf c.Value = "7FRZ" Then
            c.Value = "Ending Quantity"
        ElseIf c.Value = "2POR" Then
            c.Value = "Purchased Quantity"
        ElseIf c.Value = "8CNT" Then
            c.EntireColumn.Delete
        ElseIf c.Value = "9END." Then
            c.EntireColumn.Delete
        End If
    Next
End Sub

 

[Nine Zero]

Sub Nine0()
  Dim iCol          As Long

  With Worksheets("DATA")
    For iCol = 11 To 3 Step -1
      Select Case .Cells(8, iCol).Value
        Case "0BEG": .Cells(8, iCol).Value = "Starting Quantity"
        Case "3XFI": .Cells(8, iCol).Value = "Transfer Quantity"
        Case "4RTN": .Cells(8, iCol).Value = "Returned Quantity"
        Case "5ADI": .Cells(8, iCol).Value = "Adjusted Quantity (+)"
        Case "2SAL": .Cells(8, iCol).Value = "Sold Quantity"
        Case "3XFO": .Cells(8, iCol).Value = "Picked Quantity"
        Case "5ADO": .Cells(8, iCol).Value = "Adjusted Quantity (-)"
        Case "7FRZ": .Cells(8, iCol).Value = "Ending Quantity"
        Case "2POR": .Cells(8, iCol).Value = "Purchased Quantity"
        Case "8CNT", "9END.": .Columns(iCol).Delete
      End Select
    Next iCol
  End With
End Sub

This works perfectly, thank you very much

 

[shg]

You're welcome.