Create a new column in an existing sheet based on certain conditions

[amrita17170909]

Hi All,

I am trying to add a column by the name of bill split to an existing sheet by name of "Copied Data".

The code should be able to do following things:
1. Create a Column by name of "Bill Split" ( Location - next to a column by name of GL_Account)
2. Check what's the first number of the GL Account and on that basis columkn "Bill Split" will be populated
e.g If it starts with 1 and 2 then it should be "Capex"
If it starts with 3 and 4 it should be "Error"
If it starts with 5 it should be "Capex"

I have started writing the code but unable to understand how should my loop look like :

Sub create_bill_split()

Dim LastRow As Long

 With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
 
Worksheets("Copied Data").Activate
    
 With Worksheets("Copied Data")
 
   LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
          
  .Columns("AI").EntireColumn.Insert _
            Shift:=xlShiftToRight
            
  .Range("AI" & lSumRow + 1 & ":AI" & LastRow).Formula = "???/"
     For ctr = LastRow To 5 Step -1
            
      
    Next ctr

 End With
 
End Sub

 

[JLGWhiz]

Since I cannot see the worksheet, I am guessing based on the narrative in the OP. But give this a try for your loop.

For ctr = LastRow To 5 Step -1
    Select Case Left(.Cells(ctr, "AJ").Value, 1)
        Case 1, 2, 5
            .Cells(ctr, "AI") = "Capex"
        Case 3, 4
            Cells(ctr, "AI") = "Error"
        Case Else
            MsgBox "Criteria Less Than 1 Or Greater Than 5"
        End Select
Next ctr

 

[amrita17170909]

Thanks so much works beautifully

 

[JLGWhiz]

Thanks so much works beautifully

You're welcome,
regards, JLG