Invalid qualifier

[Martin Wheeler]

xl2000
XL does not like the code below
"A" is rejected as an invalid qualifier.
Can someone please set me straight. I have read the Help section but it
does not make sense to me.
Ta,
Martin

Dim A As Single
A = Application.WorksheetFunction.Max(.Range("CA7:CA9"))
Set B = A.Offset(0, 6)
Set C = A.Offset(0, 5)
Set D = A.Offset(0, 8)
If A > 10 And B = .Range("CG32").Value And C < 20 And D < -0.395 Then

 

[KL]

Martin,

Dim A As Single
A = Application.WorksheetFunction.Max(.Range("CA7:CA9"))

Here you are declaring A sa Single and define it as the maximum value of the
range CA7:CA9, let's say A=35

Set B = A.Offset(0, 6)
Set C = A.Offset(0, 5)
Set D = A.Offset(0, 8)

....and here you are trying to use the variable A as a range, hence

if A=35, then 35.Offset(0,6) = NONSENSE

KL

 

[Martin Wheeler]

Hi KL
Thanks for the help.
I'm good at nonsense.
Ta,
Martin

 

[Bob Phillips]

A is defined as a Single variable, and you are trying to use it as a range.
You need to get the cell that contains that Max value, something akin to

Dim A As Single
Dim APos as Long
DimARng as Range

A = Application.WorksheetFunction.Max(.Range("CA7:CA9"))
APos = WorksheetFunction.Match(A1,.Range("CA7:CA9"),0)
Set ARng = .Range("CA7:CA9").Cells(APos,1)
Set B = ARng.Offset(0, 6)
Set C = ARng.Offset(0, 5)
Set D = ARng.Offset(0, 8)
If A > 10 And B = .Range("CG32").Value And C < 20 And D < -0.395 Then

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Martin Wheeler]

Hi Bob,
Thanks for the help Bob, I had to play with the code a bit and it still
needs some fine tuning but it does run.
Ta,
Martin

 

[Martin Wheeler]

Hi Bob,
I've got it running perfectly now.
Thanks again for your help
Ta,
Martin