• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1519
  • Last Modified:

Excel VB Max & Min value + cell address

Hello Experts,

I have hit a wall in trying to get data calculated correctly in my routine.  I keep getting [Invalid Qualifer].

I want to find the max range in a column then find the address of that cell and use the offset for another cell.

Here is my code as it stands now that does not work:

Sub FindMaxMin() 'this is a sheet routine

    Dim ThisRange As Range
    Dim lRow As Long
    Dim lCol As Long
   
    For lRow = 3 To 13
      For lCol = 3 To 3     '(I cut this to 3 To 3 for testing, but it will be 3 TO 24)
         'r = 15 To 19

         ThisRange = (Range(lRow, lCol))

         Sheet4.Cells(15, lCol).Value = Sheet4.MaxAddress(ThisRange).Value
         Sheet4.cells(15, lCol -2) = cell.address.offset(,-2)

         'Next r  - I pulled this out and defaulted to row 15 just for testing.

      Next lCol

    Next lRow
   
End Sub

Function MaxAddress(ByRef ThisRange As Range) As String
   
    Dim cel As Range
   
    For Each cel In ThisRange
   
        If cel = Application.WorksheetFunction.Max(ThisRange) Then MaxAddress = cel.Address
   
    Next cel
   
   ' I need to add these functions after I get the first one to work properly.

    'FirstMax = Application.WorksheetFunction.Max(rng) - this is row 15
    'NextMax = Application.WorksheetFunction.Large(rng, 2)  - this is row 16
    'FirstMin = Application.WorksheetFunction.Min(rng)  - this is row 17
    'NextMin = Application.WorksheetFunction.Small(rng, 2) - this is row 18
   
End Function

Thanks,
Michael
0
mike637
Asked:
mike637
  • 6
  • 4
  • 2
2 Solutions
 
redmondbCommented:
Hi, mike637.

This line...
ThisRange = (Range(lRow, lCol))
...should be...
Set ThisRange = (Range(lRow, lCol))

Regards,
Brian.
0
 
mike637Author Commented:
Hi Brian,

This still errored out as bad qualifier.

Would it be easier if I sent you the workbook on this?  I really need some help or guidance.

Michael
0
 
redmondbCommented:
Apologies, mike637. I was too quick. There are two further items...

The one actually causing your error...
Sheet1.MaxAddress(ThisRange).Value
...should be...
Sheet1.MaxAddress(ThisRange)

Finally...
Cell.Address.Offset(, -2)
...is that an undefined variable?

Regards,
Brian.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
mike637Author Commented:
Brian,

The Cell.Address offset is a variable but it is always related to the address of the MaxAddress.

Michael
0
 
redmondbCommented:
<blush> And...
Set ThisRange = (Range(lRow, lCol))
...should be...
Set ThisRange = (cells(lRow, lCol))

I an issue with...
Cell.Address.Offset(, -2)
...but there's another problem - if Cell is defined (and set) as a range, you'r eprobably looking for...
Cell.Offset(, -2).Address

So, maybe posting the file would be a good idea!

Thanks,
Brian.
0
 
redmondbCommented:
mike637,

Apologies, crossing posts, so please see my previous post.

The Cell.Address offset is a variable but it is always related to the address of the MaxAddress.
From what you've posted, it's neither a defined nor a set variable. Even if its definition is elsewhere it's not connected to MaxAddress.

Regards,
Brian.
0
 
mike637Author Commented:
As requested,

Just tell me where to send the check to - so you receive it.

Michael
MonthyBusinessReview.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
Try
Sub FindMaxMin() 'this is a sheet routine

    Dim ThisRange As Range
    Dim lRow As Long
    Dim lCol As Long
   
    For lRow = 3 To 13
      For lCol = 3 To 3     '(I cut this to 3 To 3 for testing, but it will be 3 TO 24)
         'r = 15 To 19

         Set ThisRange = (Cells(lRow, lCol))

         Sheet4.Cells(15, lCol).Value = MaxAddress(ThisRange)
         Sheet4.Cells(15, lCol - 2) = Range(MaxAddress(ThisRange)).Offset(, -2)

         'Next r  - I pulled this out and defaulted to row 15 just for testing.

      Next lCol

    Next lRow
   
End Sub

Function MaxAddress(ByRef ThisRange As Range) As String
   
    Dim cel As Range
   
    For Each cel In ThisRange
   
        If cel = Application.WorksheetFunction.Max(ThisRange) Then MaxAddress = cel.Address
   
    Next cel
   
   ' I need to add these functions after I get the first one to work properly.

    'FirstMax = Application.WorksheetFunction.Max(rng) - this is row 15
    'NextMax = Application.WorksheetFunction.Large(rng, 2)  - this is row 16
    'FirstMin = Application.WorksheetFunction.Min(rng)  - this is row 17
    'NextMin = Application.WorksheetFunction.Small(rng, 2) - this is row 18
   
End Function

Open in new window

0
 
redmondbCommented:
mike637,

Two of us working on this separately is only going to cause confusion, so I'll step aside as long as ssaqibh's on it.

Regards,
Brian.
0
 
Saqib Husain, SyedEngineerCommented:
I have written my own routine called getmaxmin2 in the module1. Check it out
Copy-of-MonthyBusinessReview-3.xlsm
0
 
mike637Author Commented:
Thank you Brian and ssaquibh,

Your guidance has helped out a lot.  I split the points on this to be fair to both - expecially when both of you were taking out your time to assist.

Thanks again,
Michael
0
 
redmondbCommented:
Thanks, Michael.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now