Compile errors "End if without block If" with LOOPS

A

AshRhazaly

New member
Joined
Jul 19, 2016
Messages
9
Reaction score
0
Points
0
Hi gurus,

I've an issue with my code and I'm fairly new to VBA so pardon any amateurish mistakes.

If a cell in Overextension returns a "Yes", the code will then search the price column for the maximum value.

I'm facing a compile error "End if without block if" and curious to know what the solution is.

Code: 
Private Sub CommandButton1_Click()

Dim maximum As Double, OEResult As Range, OverExtension As Range, PriceNo As Range, PriceCol As Range, OEYes As Range


Cells.Interior.ColorIndex = 0
Set PriceCol = Range("C2:C12")
Set OverExtension = Range("D2:D11")
Set OEYes = Union(OEYes, OEResult)
OEYes = OEResult
maximum = WorksheetFunction.Max(OEYes)
    For Each OEResult In OverExtension
        If OEResult.Value = "Yes" Then
        
             
        For Each PriceNo In PriceCol
            If PriceNo.Value = maximum Then PriceNo.Cells.Interior.ColorIndex = 22
    
            End If
        End If
    
    Next PriceNo
Next OEResult
            
    
End Sub
 
?
Code: 
Private Sub CommandButton1_Click()
Dim maximum As Double, OEResult As Range, OverExtension As Range, PriceNo As Range, PriceCol As Range, OEYes As Range

Cells.Interior.ColorIndex = 0
Set PriceCol = Range("C2:C12")
Set OverExtension = Range("D2:D11")
Set OEYes = Union(OEYes, OEResult)
OEYes = OEResult
maximum = WorksheetFunction.Max(OEYes)
For Each OEResult In OverExtension
  If OEResult.Value = "Yes" Then
    For Each PriceNo In PriceCol
      If PriceNo.Value = maximum Then PriceNo.Cells.Interior.ColorIndex = 22
    Next PriceNo
  End If
Next OEResult
End Sub
but I don't see where you've assigned OEResult
 
I've done a separate code which is "cleaner" but it doesnt seem to work.

Code: 
Private Sub CommandButton1_Click()

Dim maximum As Double, OverExtension As Range, x As Range


Cells.Interior.ColorIndex = 0
Set OverExtension = Range("D2:D11")
maximum = WorksheetFunction.Max(Range("c2:c11"))


    For Each x In OverExtension
        If x.Value = "Yes" Then
            If x.Offset(0, -1).Value = maximum Then
            x.Cells.Interior.ColorIndex = 22
            End If
        End If
    Next x
    
End Sub

Ideally the code will create a new "range" for the "Yes" in overextension and determine the maximum value and color the cell red if it's the maximum value. But with the refined code I'm still getting nothing :confused:
 
Last edited by a moderator:
Seems OK here. you don't need the Cells in:

x.Cells.Interior.ColorIndex = 22
 
Can't help without a file exhibiting the error.
 
1. The code you have in message #3 is different from the code in the file, especially:

If x.Value = "Yes" Then
has become:
If x.Value = Yes Then

Keep the quote marks in.

2. The max value in column C is 4134314 and there is only one cell with that value in, and its adjacent cell contains "No". So nothing is coloured, as expected.
 
Last edited:
Thanks for the replies!

My intention however is for the code to acknowledge the "Yes" in Overextension thereby creating a new range with new prices that have "Yes" in overextension and the code will then determine the maximum value in the new range.
 
Last edited by a moderator:
AshRhazaly said:
My intention however is for the code to acknowledge the "Yes" in Overextension thereby creating a new range with new prices that have "Yes" in overextension and the code will then determine the maximum value in the new range.
Click to expand...
I have no idea what you mean. Supply a workbook with how you want things to end up.
 
try:
Code: 
Sub Macro22()
Range("H1").Value = Range("D1").Value
Range("H2").Value = "Yes"
Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("H1:H2"), CopyToRange:=Range("A15"), Unique:=False
Range("H1:H2").ClearContents
End Sub
 
I've got a Run-time error '1004': The extract range has a missing or illegal field name.

Code: 
Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("H1:H2"), CopyToRange:=Range("A15"), Unique:=False
 
Last edited by a moderator:
Please do not quote entire posts unnecessarily. They make thread hard to follow and only clutter. Thank you

( reading the FAQ might help...)
 
Is there already something at A15? There wasn't in your sample file, and that's where you wanted the new llist.
You'll probably have to change:

CopyToRange:=Range("A15")
to something else.
 
You must log in or register to reply here.
Back
Top