For Each... If Then... + Application.Max

[joseph.bloggs]

Hello guys,

I've been trying to figure this out the whole evening yesterday and today, however I'm lost. This is the 3rd time i am trying to write a macros and most of the knowledge I have is through googling snippets of what I want it to do.

What I have: Spreadsheet with column Area, containing values in ascending order. They mayb be either all positive, or half positive, half negative or all negative. Rows start most often with negative values. What I am trying to do, is

1. Find First positive value in the rows and select containing it cell, stop the for each loop as there is no need to go further.
2. If there is no positive value in the rows, do other loop and select the largest negative number from a list of negative values in the same column. I am trying to do it using the Application.max(). I then want to select the cell in which the max value is located, but can't. Seem like Find doesn't work. It returns error which I do not understand. Maybe there is a flaw in the way I designed loops?

Could you please look into this?

Sub FirstPositive()
Dim Area As Range
Dim r As Range
Dim MaxValue As Variant
Dim s As Range


Cells.Find(What:="Area", LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False _
        , SearchFormat:=False).Activate
        ActiveCell.Offset(1, 0).Activate
        Range(ActiveCell, ActiveCell.End(xlDown)).Select
Set Area = Selection
Area.Interior.ColorIndex = 0
  
 
For Each r In Area.Rows
        If r.Value > 0 Then
        r.Select
        ActiveCell.Interior.ColorIndex = 4
        Exit For
        End If
        Next
        
        
        For Each r In Area.Rows
        If r.Value < 0 Then
        
MaxValue = Application.Max(Area)
Area.Find(What:="MaxValue", LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False _
        , SearchFormat:=False).Activate
ActiveCell.Interior.ColorIndex = 17
               
        Exit For
        End If
        Next

           
     
End Sub

Thank you

 

[Comfy]

Your Finds are looking for the words "Area" and "MaxValue"

Rather than looking for the values assigned to those objects.

EG

Dim MaxValue As Long
MaxValue = 60

Cells.Find(What:="MaxValue", LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False _
        , SearchFormat:=False).Activate

Will look for MAxValue in Cells

Dim MaxValue As Long
MaxValue = 60

Cells.Find(What:=MaxValue, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False _
        , SearchFormat:=False).Activate

Will look for 60

 

[joseph.bloggs]

Hi Comfy, Thank you for taking your time.

I wanted to clarify, that the spreadsheet is long and with the first part of my Macros, I am looking for the specific Column in the spreadsheet, which is named as Area. Once found I move to the next row in that column, to where the actual values are located. I select the range in this column and name it as Area.

Only if there is no positive value in the range, I determine Max value and I look for that value in Area range. Max Value is only determined if columns contains no positive values. So it has to be determined each time, its not static.

Sorry I didn't make it clear first time.

 

[Comfy]

My example wasn't very clear either.

I wasn't referring to your setting of the range Area.
Just the finding of MaxValue.

This:

Area.Find(What:="MaxValue", LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False _
        , SearchFormat:=False).Activate

Should be this:

Area.Find(What:=MaxValue, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False _
        , SearchFormat:=False).Activate

if you want to find the number that "MaxValue" represents.

 

[joseph.bloggs]

Hi Comfy, thanks for pointing the difference between searching with "" or without them, I didn't know that.

I have just changed the code and tried running it again, however i run into another problem

Run-time error '91':
Object variable or With block variable not set

What might that be? I can't see other issues in code now and i don't use object or with blocks

 

[Comfy]

I don't get the same error but I would rewrite your code slightly.

If Application.Max(Area) > 0 Then
'Run the code to Find the first positive value
Else
'Run the code to find the greatest Negative Number
End if

Would give

Sub FirstPositive()
Dim Area As Range
Dim r As Range
Dim MaxValue As Variant
Dim s As Range


Cells.Find(What:="Area", LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False _
        , SearchFormat:=False).Activate
        ActiveCell.Offset(1, 0).Activate
        Range(ActiveCell, ActiveCell.End(xlDown)).Select
Set Area = Selection
Area.Interior.ColorIndex = 0
  
If Application.Max(Area) > 0 Then
For Each r In Area.Rows
        If r.Value > 0 Then
        r.Select
        ActiveCell.Interior.ColorIndex = 4
        Exit For
        End If
        Next
        
Else
        
MaxValue = Application.Max(Area)
Area.Find(What:=MaxValue, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False _
        , SearchFormat:=False).Activate
ActiveCell.Interior.ColorIndex = 17
               
End if
           
     
End Sub

 

[joseph.bloggs]

Hey Comfy, you know what? I understood my mistake. The range of numbers I have are calculated as a formula and they are not plaint values. That's why I was getting this sort of error. I will have to look into this, and if won't find solution I'll come back to this thread.

Thanks for helping Comfy.