Problem with Nested IF formula in macro

[sn152]

Hi All,

I am trying to apply nested IF formula using VBA. Below is the code that I used. But this code is missing few rows while filling the category. Please see the attached for more info.
Please help.

Sub Nested_If()
Dim i As Integer
Dim n As Range

Set n = Range("XFC1")
For i = 2 To n + 1

If Cells(i, 1) <= 1 Then
Cells(i, 2) = "0-1 Day"
Else
If Cells(i, 1) >= 2 And Cells(i, 1) <= 7 Then
Cells(i, 2) = "2-7 Days"
Else
If Cells(i, 1) >= 8 And Cells(i, 1) <= 10 Then
Cells(i, 2) = "8-10 Days"
Else
If Cells(i, 1) >= 11 Then
Cells(i, 2) = "+10 Days"
End If
If Cells(i, 1) = "" Then
Cells(i, 2) = ""
End If
End If
End If
End If
Next i
End Sub

 

[Hui]

I think it should be:

Sub Nested_If()
Dim i As Integer
Dim n As Range

Set n = Range("XFC1")
For i = 2 To n + 1
  If Cells(i, 1) <= 1 Then
  Cells(i, 2) = "0-1 Day"
  ElseIf Cells(i, 1) >= 2 And Cells(i, 1) <= 7 Then
  Cells(i, 2) = "2-7 Days"
  ElseIf Cells(i, 1) >= 8 And Cells(i, 1) <= 10 Then
  Cells(i, 2) = "8-10 Days"
  ElseIf Cells(i, 1) >= 11 Then
  Cells(i, 2) = "+10 Days"
  End If
 
  If Cells(i, 1) = "" Then Cells(i, 2) = ""
Next i
End Sub

 

[Hui]

You may want to also consider:

Sub Nested_If()
Dim i As Integer
Dim n As Range

Set n = Range("XFC1")
For i = 2 To n + 1
  Select Case Cells(i, 1)
  Case Is < 1
  Cells(i, 2) = "0-1 Day"
  Case Is <= 7
  Cells(i, 2) = "2-7 Days"
  Case Is <= 10
  Cells(i, 2) = "8-10 Days"
  Case Is > 11
  Cells(i, 2) = "+10 Days"
  End Select
 
  If Cells(i, 1) = "" Then Cells(i, 2) = ""
Next i
End Sub

 

[sn152]

Hello Hui,

Thanks for your reply. The first code applies the category only for the days greater than 10 and the second code misses few cells in between and appliies category for others. What could be the problem? Please advice.

 

[sn152]

It is missing all the days showing number "11".

 

[sn152]

Oh I figured it out. I just added an equal to sign as shown below. That solved my problem. Thank you so much Hui.

CaseIs >= 11
  Cells(i, 2) = "+10 Days"

 

[NARAYANK991]

Hi ,

My suggestion would be to always use :

Case Else

for the last case option , so that anything that is not satisfying the other case conditions is considered in the last option.

This is in case you do not wish to exclude any case from being handled by the Select Case statement.

Narayan

 

[Deepak]

@sn152

Set n = Range("XFC1")

Use it as

For i = 2 To Range("A2").CurrentRegion.Rows.Count

You can also use the Excel function in VBA too..

http://forum.chandoo.org/threads/nested-if-formula-getting-wrong-result.23689/#post-143334

Sub n_if()

Range("B2:B" & Range("A2").CurrentRegion.Rows.Count).Formula = _
"=IF(ISBLANK(A2),"""",LOOKUP(A2,{0,1,2,8,11},{""0-1 Day"",""0-1 Day"",""2-7 days"",""8-10 days"",""10+ days""}))"

Range("B2:B" & Range("A2").CurrentRegion.Rows.Count).Value = Range("B2:B" & Range("A2").CurrentRegion.Rows.Count).Value

End Sub

 

[sn152]

@NARAYANK991
@Deepak
Thank you so much for your help and advice. You guys are always awesome...