Entering Formula Into cell within For loop referencing the current row

[Felix1980]

Hello,
I'm having trouble with this. I'm trying to enter :

=IF(A?="","YES","NO")

Into certain cells where the question mark references whichever row the loop is on, however I'm having trouble getting the row reference into the formula. So far I have :

 Sub formval()
 
 Dim c As Range
 Dim MyRange As Range
 Dim MyFormula As String
 lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
 Set MyRange = Range("A1:A" & lastrow)
 'MyFormula = "=IF(A" & Row & "= " & Chr(34) & "," & Chr(34) & "YES" & Chr(34) & "," & Chr(34) & "NO" & Chr(34) & ")"
' MyFormula = "=IF(A" & Row & "="""",""YES"",""NO"")"
 'MyFormula = "A" & c.Offset(0, 0)
 'MyFormula = c.Formula

 For Each c In MyRange

  If InStr(c.value, "Load No.") > 0 Then
     c.EntireRow.Interior.ColorIndex = 17

ElseIf InStr(c.value, "Dispt. Lim.") > 0 Then
     c.EntireRow.Interior.ColorIndex = 17
     
ElseIf Not InStr(c.value, "Load No.") Or InStr(c.value, "Load Date") Then
    ' c.Offset(0, 20).value = "=IF(A:" & Row & "=""""" & ",""YES"",""NO"")"
    ' c.Offset(0, 20).value = MyFormula
   '  c.Offset(0, 20).value = "=IF(A" & Row & "= " & Chr(34) & "," & Chr(34) & "YES" & Chr(34) & "," & Chr(34) & "NO" & Chr(34) & ")"
  '   "=IF(A" &row & "=" & chr(34) &",""YES","NO")

 End If
 Next
  
End Sub

Where all the "comments" are failed tries. If someone could please point to what I'm doing wrong I would greatly appreciate it.

 

[lrobbo314]

I would avoid the loop entirely and just use VBA to insert the formula. Then I would handle the cell colors with conditional formatting.
Looping through the cells like that and evaluating string values is incredibly inefficient.

Sub FX()
With Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(, 19)
    .FormulaR1C1 = "=IF(RC[-19]="""",""YES"",""NO"")"
    .Value = .Value
End With
End Sub

 

[Felix1980]

Hello Irobbo,

This code is part of a bigger whole which is run everyday to add and edit more data to the bottom of an ongoing list. I removed part of the code to use less lines but there are lines adding to headings to certain columns if the first column has those names. Is there a way to do all of that with a with statement? Please see below for the full code of this part :

 Sub checll()
 

 Dim c As Range
 Dim MyRange As Range
 lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
 Set MyRange = Range("A1:A" & lastrow)
 For Each c In MyRange

  If InStr(c.value, "Load No.") > 0 Then
     c.EntireRow.Interior.ColorIndex = 17
     c.Offset(0, 1).value = "SO#"
     c.Offset(0, 2).value = "Load No."
     c.Offset(0, 3).value = "Plate"
     c.Offset(0, 4).value = "Distribution Centre"
     c.Offset(0, 5).value = "Customer"
     c.Offset(0, 6).value = "City"
     c.Offset(0, 7).value = "Province"
     c.Offset(0, 8).value = "Carrier"
     c.Offset(0, 11).value = "Dispatched Qty"
     c.Offset(0, 12).value = "Load Closing Date"
     c.Offset(0, 14).value = "Dispatched Weight"
     c.Offset(0, 15).value = "Animal Type"
     c.Offset(0, 16).value = "CHEP Total"
     c.Offset(0, 17).value = "Uploaded?"
     c.Offset(0, 18).value = "Invoice #"
     c.Offset(0, 19).value = "Invoice Amount"
     c.Offset(0, 20).value = "Accrue"
     c.Offset(0, 21).value = "Notes"
ElseIf InStr(c.value, "Dispt. Lim.") > 0 Then
     c.EntireRow.Interior.ColorIndex = 17
     
ElseIf Not InStr(c.value, "Load No.") Or InStr(c.value, "Dispt. Lim.") Then
     'c.Offset(0, 20).value = "=IF(A:" & c.value & "=""""" & ",""YES"",""NO"")"

     
     
 End If
 Next
  
End Sub

The full code pulls another workbooks sheet which contains the data from the previous day, then changes elements, adds others and deletes irrelevant lines and adds it to the bottom of the current information in the final workbook.

 

[Felix1980]

Hello, The code you gave is great, but I need to add it only on certain cells.

Above is pretty much what the finished will look like with the bolded cells holding information I have yet to figure out how to code.

The Accrue column should have the formula such as above only on lines that have numbers in column B. The above suggested code works to put the formula into every row but I cannot figure out how to change with statements to work with an if type of situation.

I'm trying, unsuccessfully, to figure out the code to add the total qty and weight in H and J for only the lines between each group of yellow line, for example :

H2 should equal the sum of H3 + 4 and H13 should equal the sum of H14-18

Any help is really appreciated.