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

joseph.bloggs

New Member
Joined
Nov 7, 2011
Messages
4
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?

Code:
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
 
Last edited:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Your Finds are looking for the words "Area" and "MaxValue"

Rather than looking for the values assigned to those objects.

EG

Code:
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

Code:
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
 
Upvote 0
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.
 
Upvote 0
My example wasn't very clear either.

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

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

Should be this:
Code:
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.
 
Upvote 0
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
 
Upvote 0
I don't get the same error but I would rewrite your code slightly.

Code:
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

Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,167
Messages
6,170,469
Members
452,330
Latest member
AFK_Matrix

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