VBA Compiler Error: Invalid Qualifier

[Ashfranc0223]

When I execute the VBA code that is given below, I get an error "Compiler Error: Invalid Qualifier" and the name of subprocedure gets highlighted. Why does this happen? I have attached a copy of the code below:

Option Explicit
Sub TestA()


Dim numberrange As Range
Dim c As Range, maxv As Long, maxa As String


Set numberrange = Application.InputBox("Select a range to find max value", "Max Value", , , , , , 8)


maxv = numberrange.Cells(1, 1).Value
maxa = numberrange.Cells(1, 1).Address




For Each c In numberrange.Value
    If c.Value > maxv.Value Then
    c.Value = maxv.Value
    c.Address = maxa.Address
    End If
Next c


MsgBox ("max:" & maxv)


End Sub

 

[Tim_Excel_]

[COLOR=#333333]c.Value = maxv.Value[/COLOR]
[COLOR=#333333]c.Address = maxa.Address[/COLOR]

You're asking the Value and Address of two variables you already explicitly made Values and Addresses in

[COLOR=#333333]maxv = numberrange.Cells(1, 1).Value[/COLOR]
[COLOR=#333333]maxa = numberrange.Cells(1, 1).Address[/COLOR]

Furthermore, the .Address property will return the column(s) and row(s) of whatever range it's attached to. Asking the address from Cells(1,1) will always return the same address, being $A$1.

 

[Rick Rothstein]

When I execute the VBA code that is given below, I get an error "Compiler Error: Invalid Qualifier" and the name of subprocedure gets highlighted. Why does this happen? I have attached a copy of the code below:

Option Explicit
Sub TestA()

Dim numberrange As Range
Dim c As Range, maxv As Long, maxa As String

Set numberrange = Application.InputBox("Select a range to find max value", "Max Value", , , , , , 8)

maxv = numberrange.Cells(1, 1).Value
maxa = numberrange.Cells(1, 1).Address

For Each c In numberrange.Value
If c.Value > maxv.Value Then
c.Value = maxv.Value
c.Address = maxa.Address
End If
Next c

MsgBox ("max:" & maxv)

End Sub

The variable maxv is declared as Long... it does not have a Value property. Try removing the .Value from the two red highlighted text above.

Similarly, the variable maxa is declared as a String... it does not have an Address property. Try removing the .Address from the blue highlighted text above.

 

[Yongle]

An alternative for you to consider after resolving the issues in your current code
- avoids looping
- uses Max function

Sub TestB()
    Dim NumberRange As Range, c As Range, v As Range, maxV As Double
    Set NumberRange = Application.InputBox("Select a range to find max value", "Max Value", , , , , , 8)
    maxV = WorksheetFunction.Max(NumberRange)
    Set v = NumberRange.Find(maxV, LookAt:=xlWhole)
    MsgBox "Max value = " & maxV & " in " & v.Address(0,0)
End Sub