Simple constraint problem...

[Mcneilius]

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

 

[Guest]

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...

 

[Guest]

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.)