IF....., If....., If.......else VBA

[SAMCRO2014]

I am trying to create coding that will loop through column L (12) and if the cell contains the specific text or value then enter "Previous" or "Current" in adjacent cell in column M (13):

"Error" = Current
"[Rr]etro = Previous
> 201814 = Current
all other cells = Previous

Dim f As Long ' for fiscal

Dim Data As Worksheet
Set Data = ThisWorkbook.Sheets("Data")

Dim LastRowData As Long
LastRowData = Data.Cells(Rows.Count, 12).End(xlUp).Row


For f = 2 To LastRowData

If Cells(f, 12).Value Like "*[Rr]etro*" Then
Cells(f, 13) = "Previous"
If Cells(f, 12).Value Like "*Error*" Then
Cells(f, 13) = "Current"
ElseIf Cells(f, 12).Value > 201814 Then
Cells(f, 13) = "Current"
Else: Cells(f, 13) = "Previous"

End If

Next f

I think I may have too many arguments?

 

[JackDanIce]

Untested, however, try:

Sub M1()

    Dim x   As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("Data")
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 12).End(xlUp).Row
        .Cells(2, 13).Resize(x - 1).Value = "Previous"
        
        With .Cells(1, 12).Resize(x, 2).AutoFilter
            .AutoFilter field:=1, Criteria1:="*Error*"
            .Offset(1, 1).Resize(x - 1, 1).SpecialCells(xlCellTypeVisible).Value = "Current"
            .ShowAllData
            .AutoFilter field:=1, Criteria1:=">" & 201814
            .Offset(1, 1).Resize(x - 1, 1).SpecialCells(xlCellTypeVisible).Value = "Current"
        End With
        
        .AutoFilterMode = False
    End With
    
    Application.ScreenUpdating = True
    
End Sub

Macro defaults all values in column 13 to "Previous" and then filters only for *Error* and values > 201814 in column 12 and replaces values in column 13 with "Current"

 

[Fluff]

Another option

Sub test1()
With Range("M2", Range("L" & Rows.count).End(xlUp).Offset(, 1))
   .Value = Evaluate(Replace("if((@>201814)+(isnumber(search(""error"",@))),""Current"",""Previous"")", "@", .Offset(, -1).Address))
End With
End Sub

 

[SAMCRO2014]

VBA does not like the coding:

.AutoFilter field:=1, Criteria1:="*Error*"

it is giving me a compile error

I really like the idea of this coding though. I tried to use tag to your post but I could not figure how to

 

[JackDanIce]

You can look up "VBA autofilter" find some code and adapt or record it and review the resulting code.

Fluff's suggestion is shorter and one I'm trying to learn/use more often with EVALUATE but with both, they're not looping, so both are alternate ways to test data and output results

 

[SAMCRO2014]

Another option

Sub test1()
With Range("M2", Range("L" & Rows.count).End(xlUp).Offset(, 1))
   .Value = Evaluate(Replace("if((@>201814)+(isnumber(search(""error"",@))),""Current"",""Previous"")", "@", .Offset(, -1).Address))
End With
End Sub

This coding did not work. The cells containing the word "[Rr]etro are showing as "Current" when it shoud be "Previous"

 

[Fluff]

How about

Sub test1()
With Range("M2", Range("L" & Rows.count).End(xlUp).Offset(, 1))
   .Value = Evaluate(Replace("if(((isnumber(@))*(@>201814))+(isnumber(search(""error"",@))),""Current"",""Previous"")", "@", .Offset(, -1).Address))
End With
End Sub

 

[SAMCRO2014]

How about

Sub test1()
With Range("M2", Range("L" & Rows.count).End(xlUp).Offset(, 1))
   .Value = Evaluate(Replace("if(((isnumber(@))*(@>201814))+(isnumber(search(""error"",@))),""Current"",""Previous"")", "@", .Offset(, -1).Address))
End With
End Sub

It did change the cells containing the word "retro" to Previous but it also change any cells in that column with text to Previous including "Error"

 

[Fluff]

This is what I get

Excel 2013 32 bit
	L	M
2	E05004787	Previous
3	ab error yz	Current
4	123	Previous
5	Janerroruary	Current
6	January	Previous
7	[Rr]etro	Previous
8	201815	Current
9	12[Rr]etro89	Previous
10	ERROR	Current
11	1234567	Current
Sheet1

If it's not working for you, can you supply some sample data. If you follow the link in my signature, you can find some tools that will enable you to do that