VBA If...Else If unable to return value to cells met condition

[Ann Ooi]

Hi, I have the data sheet "DataCompile", which I tried to use if...else if to return value into column M, if the criteria in column N is met. However, no matter how I change the coding, it will not return other value, and only return the largest value, which is "60%><=100% Yield".
What should I do to rectify the coding below, so that it can populate every cell with correct value, that is meeting the criteria?

Sub FillIF()

Application.ScreenUpdating = False

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

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 Long
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

 

[jasonb75]

I can see one line that needs chaning,

Dim yield As Double

Long only accepts integers, not decimals.

 

[eduzs]

(answered)

 

[Ann Ooi]

I can see one line that needs chaning,

Dim yield As Double

Long only accepts integers, not decimals.

HI, I changed as per advised, but issue persist.
And I take out on error resume next, when it comes to the next condition met the criteria, it will prompt debug error "run-time 1004", and pointed to "w2.Range("N" & i).Value = Result". I do not know what is the issue here.

 

[jasonb75]

Something that I missed earlier, try adding an apostrophe to the start of each of your result strings.

Because they start with = excel thinks that they are formulas, as they are not valid formulas that will cause an error.

 

[eduzs]

Chek for locked cells in "N" & i range;

 

[Ann Ooi]

Something that I missed earlier, try adding an apostrophe to the start of each of your result strings.

Because they start with = excel thinks that they are formulas, as they are not valid formulas that will cause an error.

do you mean like this? 'Result = "60%><=100% Yield" . it will green out and not working

 

[eduzs]

Result = "'60%><=100% Yield"

 

[Ann Ooi]

Chek for locked cells in "N" & i range;

I did not lock anything, if I change the value to greater than 60% then it works, it just don't works for those cell lesser than 60%.

 

[jasonb75]

Not quite, it needs to be inside the double quotes to that is evaluated as a text comment when it is passed to the sheet rather than being a comment in vba.

I've added it in red below to try and make it stand out a little better. You shouldn't need it in this one, although it will do no harm, but the other 2 definitely need it.

Result = "'60%><=100% Yield"