Adding a formula to a VBA loop

[jassybun]

Hello - I am trying to combine a formula to a VBA loop, to add a "yes" or "no" to the next column, c.offset(,13)

=IF(D2-(C2+TIMEVALUE(L2)+IF(M2="PM",0.5,0))>1,"Yes","No")

to

Sub Lookup()
  Dim rng  As Range
  Dim c  As Range
  Dim result  As String

  Set rng = Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
 
  For Each c In rng.Cells
  On Error Resume Next
  result = Application.WorksheetFunction.VLookup(c.Value, Workbooks("Lookup.xlsx").Worksheets("Sheet1").Range("A2:B350"), 2, False)
  If Err.Number <> 0 Then result = "Not Found"
  On Error GoTo 0
 
  c.Offset(, 12).Value = result
  result = vbNullString
 
  Next c
End Sub

 

[YasserKhalil]

You can use this line

c.Offset(, 13).Formula = "=IF(D2-(C2+TIMEVALUE(L2)+IF(M2=""PM"",0.5,0))>1,""Yes"",""No"")"

 

[Fluff13]

Maybe

Sub Lookup()
  Dim rng  As Range
  Dim c  As Range
  Dim result  As Variant
 
  Set rng = Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
 
  For Each c In rng.Cells
      result = Application.WorksheetFunction.vlookup(c.Value, Workbooks("Lookup.xlsx").Worksheets("Sheet1").Range("A2:B350"), 2, False)
      If IsError(result) Then
        c.Offset(, 12) = "Not Found"
      Else
        c.Offset(, 12).Value = result
  Next c
  With rng.Offset(, 13)
      .Value = Evaluate("IF(" & .Offset(, -11).Address & "-(" & .Offset(, -12).Address & "+TIMEVALUE(" & .Offset(, -3).Address & ")+IF(" & .Offset(, -2).Address & "=""PM"",0.5,0))>1,""Yes"",""No"")")
  End With
End Sub

 

[jassybun]

Fluff, I get a an error saying there is a Next without For, at the Next C line

 

[Fluff13]

oops missed an End If, try

Sub Lookup()
  Dim rng  As Range
  Dim c  As Range
  Dim result  As Variant
 
  Set rng = Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
 
  For Each c In rng.Cells
      result = Application.WorksheetFunction.vlookup(c.Value, Workbooks("Lookup.xlsx").Worksheets("Sheet1").Range("A2:B350"), 2, False)
      If IsError(result) Then
        c.Offset(, 12) = "Not Found"
      Else
        c.Offset(, 12).Value = result
      End If
  Next c
  With rng.Offset(, 13)
      .Value = Evaluate("IF(" & .Offset(, -11).Address & "-(" & .Offset(, -12).Address & "+TIMEVALUE(" & .Offset(, -3).Address & ")+IF(" & .Offset(, -2).Address & "=""PM"",0.5,0))>1,""Yes"",""No"")")
  End With
End Sub

 

[jassybun]

Thank you - this is so helpful to learn.

Also one more question, if I want this formula to only update when I add new rows in that have not had this formula run on them, and leave alone the ones that already have had this code ran on them (column n and o) - how do I change that?

 

[Fluff13]

Try

Sub Lookup()
  Dim rng  As Range
  Dim c  As Range
  Dim result  As Variant
 
  Set rng = Range(Range("N" & Rows.Count).End(xlUp).Offset(1, -12), Range("B" & Rows.Count).End(xlUp))
  For Each c In rng.Cells
      result = Application.WorksheetFunction.vlookup(c.Value, Workbooks("Lookup.xlsx").Worksheets("Sheet1").Range("A2:B350"), 2, False)
      If IsError(result) Then
        c.Offset(, 12) = "Not Found"
      Else
        c.Offset(, 12).Value = result
      End If
  Next c
  With rng.Offset(, 13)
      .Value = Evaluate("IF(" & .Offset(, -11).Address & "-(" & .Offset(, -12).Address & "+TIMEVALUE(" & .Offset(, -3).Address & ")+IF(" & .Offset(, -2).Address & "=""PM"",0.5,0))>1,""Yes"",""No"")")
  End With
End Sub