0

I am using office 365 and I am creating a WB for someone who is using excel 2016. My code inserts a "IFS" formula in all the cells of a column to the last row. The issues is IFS is not available in 2016 so the code fails for them but works for me. Is there a way to change the following code to work in 2016? When they run the macro the get #NAME?

Dim Lastrow As Long
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("G2").Select
ActiveCell.FormulaR1C1 = _
    "=IFS(RC[-2]=""2nd Shift 1.05"",""2"", RC[-2]=""2nd Shift     
Overtime"",""2"",TRUE,"""")" 
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G" & Lastrow)
Range("E2").Select
End Sub
Dennis
  • 1
  • 2
  • 2
    use a standard IF with OR: `IF(OR(E2 = "2nd Shift 1.05",E2="2nd Shift Overtime"),2,"")` I will let you convert it to vba. – Scott Craner May 17 '19 at 14:21

2 Answers2

0

Your code could be shortened to:

Dim Lastrow As Long

Lastrow = Range("A" & Rows.Count).End(xlUp).Row

Range("G2:G" & Lastrow).Formula = "=IF(OR(E2 = ""2nd Shift 1.05"",E2=""2nd Shift Overtime""),2,"""")"
Tom
  • 9,725
  • 3
  • 31
  • 48
  • Thanks guys - I actually went in a different direction which seems to work better. I guessing it could probably be shortened as well... Im self taught and tend to do things the long way. – Dennis May 17 '19 at 15:24
0
   Dim LastCall As Long
    Dim y As Long
    LastCall = Range("A" & Rows.Count).End(xlUp).Row
    For y = 2 To LastCall
        If Range("E" & y).Value = "2nd Shift 1.05" Then
            Range("G" & y).Value = "2"
    End If
    Next y

    LastCall = Range("A" & Rows.Count).End(xlUp).Row
    For y = 2 To LastCall
        If Range("E" & y).Value = "2nd Shift Overtime" Then
            Range("G" & y).Value = "2"
    End If
    Next y
End Sub
Dennis
  • 1
  • 2