Hi guys

Hope you can help with a quick query - think I've missed somethin
basic cos I've been working on this all week!

I have a column of numbers with a rank (A to X) associated with each
let's say

col A col B
1.2 A
1.1 B
1.0 C
0.9 D
...

and I have a variable, called 'checkcase', which can be any number lik
1.093. What I'd like to do is have the code run down the list in colum
A, and select the row (and hence rank) that contains the number closes
to the variable (1.1, corresponding to B in this case. In addition, i
the number is outside the range of ranks, I want it to do somethin
different.

My attempt at the code is:


Sheets("Sheet1").Range("A1").Activate

If checkcase 1.2

' Say the value is too high for the ranking system

Else If checkcase < -1.2

' Say the value is too low for the ranking system

Else

Do While ((ActiveCell.Value - checkcase)^2 0.0025)
ActiveCell.Offset(1, 0).Activate
Loop

End If

Which works fine, until the variable lies exactly between 2 ranks, sa
1.15. It says "type mismatch". Presumably because the argument i
exactly 0.0025 in this case. I tried to solve it with a simple "="
but no luck; it must be getting confused. In this special case o
activecell.value - checkcase being 0.0025, ideally I'd like it t
select the lower rank.

Have a feeling this is a simple mistake / syntax issue - I'm a bit of
newbie to VBA.

Has anyone got any ideas on what is happening here, or if indeed ther
is a better way to handle this?

Thanks for your time!

Nei

--
Mcneiliu
-----------------------------------------------------------------------
Mcneilius's Profile: http://www.excelforum.com/member.php...fo&userid=1377
View this thread: http://www.excelforum.com/showthread.php?threadid=44802

Give this a try...
Sub test()
Call FindClosest(1.093)

End Sub

Sub FindClosest(ByVal NumberToFind As Double)
Dim rngClosest As Range
Dim rngCurrent As Range
Dim rngToSearch As Range
Dim wks As Worksheet

Set wks = ActiveSheet
Set rngToSearch = Range(wks.Range("A2"), _
wks.Cells(Rows.Count, "A").End(xlUp))

Set rngClosest = wks.Range("A2")
For Each rngCurrent In rngToSearch
If Abs(rngCurrent.Value - dblNumberToFind) < _
Abs(rngClosest.Value - dblNumberToFind) Then Set rngClosest =
rngCurrent
Next rngCurrent
rngClosest.Select
End Sub

I am on my way home for the day so if you need more help you are on your own
unitl tomorrow...
--
HTH...

Jim Thomlinson


"Mcneilius" wrote:


Hi guys

Hope you can help with a quick query - think I've missed something
basic cos I've been working on this all week!

I have a column of numbers with a rank (A to X) associated with each,
let's say

col A col B
1.2 A
1.1 B
1.0 C
0.9 D
...

and I have a variable, called 'checkcase', which can be any number like
1.093. What I'd like to do is have the code run down the list in column
A, and select the row (and hence rank) that contains the number closest
to the variable (1.1, corresponding to B in this case. In addition, if
the number is outside the range of ranks, I want it to do something
different.

My attempt at the code is:


Sheets("Sheet1").Range("A1").Activate

If checkcase 1.2

' Say the value is too high for the ranking system

Else If checkcase < -1.2

' Say the value is too low for the ranking system

Else

Do While ((ActiveCell.Value - checkcase)^2 0.0025)
ActiveCell.Offset(1, 0).Activate
Loop

End If

Which works fine, until the variable lies exactly between 2 ranks, say
1.15. It says "type mismatch". Presumably because the argument is
exactly 0.0025 in this case. I tried to solve it with a simple "=",
but no luck; it must be getting confused. In this special case of
activecell.value - checkcase being 0.0025, ideally I'd like it to
select the lower rank.

Have a feeling this is a simple mistake / syntax issue - I'm a bit of a
newbie to VBA.

Has anyone got any ideas on what is happening here, or if indeed there
is a better way to handle this?

Thanks for your time!

Neil


--
Mcneilius
------------------------------------------------------------------------
Mcneilius's Profile: http://www.excelforum.com/member.php...o&userid=13776
View this thread: http://www.excelforum.com/showthread...hreadid=448021

Consider a non-VBA approach or one that you might adapt to VBA..

1. fill column C with the absolute value of the difference between checkcase
and the value in column A

2. sort by column C ascending

3. pick the top row (the one with the minimum abs. diff.)
--
Gary''s Student


"Mcneilius" wrote:


Hi guys

Hope you can help with a quick query - think I've missed something
basic cos I've been working on this all week!

I have a column of numbers with a rank (A to X) associated with each,
let's say

col A col B
1.2 A
1.1 B
1.0 C
0.9 D
...

and I have a variable, called 'checkcase', which can be any number like
1.093. What I'd like to do is have the code run down the list in column
A, and select the row (and hence rank) that contains the number closest
to the variable (1.1, corresponding to B in this case. In addition, if
the number is outside the range of ranks, I want it to do something
different.

My attempt at the code is:


Sheets("Sheet1").Range("A1").Activate

If checkcase 1.2

' Say the value is too high for the ranking system

Else If checkcase < -1.2

' Say the value is too low for the ranking system

Else

Do While ((ActiveCell.Value - checkcase)^2 0.0025)
ActiveCell.Offset(1, 0).Activate
Loop

End If

Which works fine, until the variable lies exactly between 2 ranks, say
1.15. It says "type mismatch". Presumably because the argument is
exactly 0.0025 in this case. I tried to solve it with a simple "=",
but no luck; it must be getting confused. In this special case of
activecell.value - checkcase being 0.0025, ideally I'd like it to
select the lower rank.

Have a feeling this is a simple mistake / syntax issue - I'm a bit of a
newbie to VBA.

Has anyone got any ideas on what is happening here, or if indeed there
is a better way to handle this?

Thanks for your time!

Neil


--
Mcneilius
------------------------------------------------------------------------
Mcneilius's Profile: http://www.excelforum.com/member.php...o&userid=13776
View this thread: http://www.excelforum.com/showthread...hreadid=448021

Hi guys

Sorry the site has been playing up for me today, so couldn't reply.
This is part of a much bigger project, so I have to pursue the VBA
route. That code looks good, but I'm not good enough to figure out how
it works and it comes up with a missing "For" error.

Do you think it would work for me, if I replaced my random number
(1.093!) with a variable!?

Thanks

Neil


--
Mcneilius
------------------------------------------------------------------------
Mcneilius's Profile: http://www.excelforum.com/member.php...o&userid=13776
View this thread: http://www.excelforum.com/showthread...hreadid=448021