If statement problems

AlexCS

Board Regular
Joined
Oct 31, 2011
Messages
78
Hi all,

Can anyone tell me what is worng with the following piece of code? Even though the code under both conditions works well spearately, however I construct the If statement, Excel never gets to execute the Else part. Please help!

For i = 2 To nrLines
Dim Val As Long
Val = Sheets("I Pairs").Cells(i, 1).Value
Sheets("Lin Car").Select
ActiveSheet.Range(Range("A1"), Cells.SpecialCells(xlCellTypeLastCell)).AutoFilter Field:=19, Criteria1:=Val
If Not Val Then
MsgBox "No records found" & " " & Val
ElseIf Val Then
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "S").End(xlUp).Row
End With
Cells(LastRow, 19).EntireRow.Select
Selection.Rows.Interior.ColorIndex = 38
End If
Next i

Thanks a lot,

Alex
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
can you try this....


For i = 2 To nrLines
Dim Val As Long
Val = Sheets("I Pairs").Cells(i, 1).Value
Sheets("Lin Car").Select
ActiveSheet.Range(Range("A1"), Cells.SpecialCells(xlCellTypeLastCell)).AutoFilter Field:=19, Criteria1:=Val
If Not Val Then
MsgBox "No records found" & " " & Val
End If
If Val Then
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "S").End(xlUp).Row
End With
Cells(LastRow, 19).EntireRow.Select
Selection.Rows.Interior.ColorIndex = 38
End If
Next i
 
Upvote 0
Hi Jay,

Thank you so much for your reply..I tried doing two separate ifs but now Excel is performing both the coloring and the MsgBox if Val is found..I cannot find any logical explanation for this :confused:

Is there anything else you believe I should try?

Alex
 
Upvote 0
and how about this one....


For i = 2 To nrLines
Dim Val As Long
Val = Sheets("I Pairs").Cells(i, 1).Value
Sheets("Lin Car").Select
ActiveSheet.Range(Range("A1"), Cells.SpecialCells(xlCellTypeLastCell)).AutoFilter Field:=19, Criteria1:=Val
If Not Val Then
MsgBox "No records found" & " " & Val
Else
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "S").End(xlUp).Row
End With
Cells(LastRow, 19).EntireRow.Select
Selection.Rows.Interior.ColorIndex = 38
End If
Next i
 
Upvote 0
How about

Code:
Dim Val As Long, rng As Range
    Sheets("Lin Car").Select
For i = 2 To nrLines
    Val = Sheets("I Pairs").Cells(i, 1).Value
With ActiveSheet
    .Range(Range("A1"), Cells.SpecialCells(xlCellTypeLastCell)).AutoFilter Field:=19, Criteria1:=Val
Set rng = .AutoFilter.Range
If rng.Columns(1).SpecialCells(xlCellTypeVisible).Count = 1 Then
    MsgBox "No Records found " & Val
Else
    .Cells(.Rows.Count, "A").End(xlUp).Rows.EntireRow.Interior.ColorIndex = 38
End If
End With
Next

I assume that there is more code as your variable nrLines is currently undefined.
 
Upvote 0
Hi Jay and Jason,

Thanks for trying to help, I was really getting desperate.

@Jay, I tried all possible combinations of If/Else statements but nothing worked since the problem was probably the "Not Val" Part
@Jason, I tried the Rng approach also, but Excel would either not let me set it up or would not accept rng at an argument..your code is slightly different so it must work

In the end, I settled for this:

Sheets("I Pairs").Select
ActiveSheet.Cells(1, 1).CurrentRegion.Select
nrLines = Selection.Rows.Count

For i = 2 To nrLines
Dim Val As Long
Val = Sheets("I Pairs").Cells(i, 1).Value
Sheets("Lin Car").Select
ActiveSheet.Range(Range("A1"), Cells.SpecialCells(xlCellTypeLastCell)).AutoFilter Field:=19, Criteria1:=Val
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "S").End(xlUp).Row
End With
If LastRow > 1 Then
Cells(LastRow, 19).EntireRow.Select
Selection.Rows.Interior.ColorIndex = 38
Else: MsgBox Val
End If

Next i

Thanks once again and have a good day!

Alex
 
Upvote 0

Forum statistics

Threads
1,225,073
Messages
6,182,700
Members
453,132
Latest member
nsnodgrass73

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top