Loop column E if cell non-blank, then insert formula on other column

[Ann Ooi]

Hi, I have these VBA code that I run through each range to divide and insert if formula. Initial it has no issue, but now I have new lines, which I do not need to insert the formula if column E (Qty) is empty. And I do not know how to apply the command, if column E is non blank, then apply the rules, into current code to ensure it runs smoothly.

Sub Dividefast()

Dim w2 As Worksheet
Dim m As Range
Dim lastrow1 As Long
Dim lastm As Long

Application.ScreenUpdating = False

Set w2 = Sheets("DataCompile")

On Error Resume Next

lastrow1 = w2.Cells(Cells.Rows.Count, "A").End(xlUp).Row
lastm = Range("M" & lastrow1).End(xlUp).Row + 1

For Each m In w2.Range("M" & lastm & ":M" & lastrow1)

    m.Offset(, 0).Value = m.Offset(, -6) / m.Offset(, -8)
Next
On Error GoTo 0

Application.ScreenUpdating = True

End Sub

Sub FillYieldGrp()

Dim w2 As Worksheet
Dim LastRow As Long
Dim StartRow As Long

Application.ScreenUpdating = False

Set w2 = Sheets("DataCompile")

On Error Resume Next

LastRow = w2.Cells(w2.Rows.Count, 1).End(xlUp).Row
StartRow = w2.Cells(w2.Rows.Count, 14).End(xlUp).Row + 1

Dim i As Long
Dim Yield As Double
Dim Result As String

For i = StartRow To LastRow

 Yield = w2.Range("M" & i).Value
       
 If Yield >= 0.6 And Yield <= 1 Then
    Result = "'60%><=100% Yield"
    
 ElseIf Yield >= 0.3 And Yield < 0.6 Then
    Result = "'<60% Yield"
    
  Else
    Result = "'<30% Yield"
    
 End If
 
  w2.Range("N" & i).Value = Result
  
Next

Application.ScreenUpdating = True

End Sub

 

[DanteAmor]

I put your 2 macros together in a single macro. The next one goes through the entire column E and puts the result in M and N.

Sub Loop_Column_E()
  Dim sh As Worksheet, i As Long
  Dim a As Variant, b As Variant
  
  Set sh = Sheets("DataCompile")
  a = sh.Range("E2:G" & sh.Range("E" & Rows.Count).End(3).Row).Value2
  ReDim b(1 To UBound(a), 1 To 2)
  
  For i = 1 To UBound(a)
    If a(i, 1) <> "" Or a(i, 1) <> 0 Then
      b(i, 1) = a(i, 3) / a(i, 1)
      Select Case b(i, 1)
        Case Is < 0.3: b(i, 2) = "'<30% Yield"
        Case Is < 0.6: b(i, 2) = "'<60% Yield"
        Case Else:     b(i, 2) = "'60%><=100% Yield"
      End Select
    End If
  Next
  
  sh.Range("M2").Resize(UBound(b), 2).Value = b
End Sub

 

[Ann Ooi]

I put your 2 macros together in a single macro. The next one goes through the entire column E and puts the result in M and N.

Sub Loop_Column_E()
  Dim sh As Worksheet, i As Long
  Dim a As Variant, b As Variant
 
  Set sh = Sheets("DataCompile")
  a = sh.Range("E2:G" & sh.Range("E" & Rows.Count).End(3).Row).Value2
  ReDim b(1 To UBound(a), 1 To 2)
 
  For i = 1 To UBound(a)
    If a(i, 1) <> "" Or a(i, 1) <> 0 Then
      b(i, 1) = a(i, 3) / a(i, 1)
      Select Case b(i, 1)
        Case Is < 0.3: b(i, 2) = "'<30% Yield"
        Case Is < 0.6: b(i, 2) = "'<60% Yield"
        Case Else:     b(i, 2) = "'60%><=100% Yield"
      End Select
    End If
  Next
 
  sh.Range("M2").Resize(UBound(b), 2).Value = b
End Sub

Hi Dante,
Thank you so much, I do not know how the magic works. But it just run perfectly for my macro file. You saved my day. Wish you a great day ahead!

 

[DanteAmor]

I'm glad to help you. You too have a good day.