VBA - finding a cell closest to a specific value

[Gnera]

Hello!

I've written a code that finds the cell closest to 0 in a range, marks it and then uses it in calculations. However, my code works only if the cell closest to 0 is not the first cell. When the closest value is in the first cell, it doesn't mark the cell and the results of the calculations are wrong.

The relevant part of my code

Dim mx As Single                    'maximum value in the range [I]rng[/I]
Dim cell As Range                   'each cell in the range [I]rng[/I]
Dim phase As String                 'cell with the maximum value in the range [I]rng[/I]
Const zero As Integer = 0           'the value I want the cell to be the closest to


Set rng = Range(Cells(row, i), Cells(j, i))        
    mx = Application.Max(rng)
        
For Each cell In rng
    If Abs(zero - cell) < mx Then
         mx = Abs(zero - cell)
         phase = cell.Address
    End If
    Next cell
    
    Range(phase).Offset(, 1) = "x"         'to mark the cell
    Cells(22, i + 1) = (Range(phase).Offset(0, -3).Value) - ((((Range(phase).Value) - zero) / ((Range(phase).Value) - (Range(phase).Offset(1, 0).Value))) * ((Range(phase).Offset(0, -3).Value) - (Range(phase).Offset(1, -3).Value)))    'the calculation

Next i

Hopefully someone will spot the mistake and let me know!

 

[JoeMo]

I don't know how to see what it gives for the value for phase when running the debugger.

Data sample:
to both A1:A5 and D1:D5 put
-1
-2
-3
-4
-5

Data sample code

Sub MACRO()


Dim row
Dim mx As Single
Dim cell As Range
Dim phase As String
Const zero As Integer = 0
    
row = 1   'start row
    
For i = 1 To 4 Step 3   'if i=1 --> column A, if i=4 --> column D
    j = Cells(row, i).End(xlDown).row   'find the last used cell in a column to get the range rng lenght
    
    Set Rng = Range(Cells(row, i), Cells(j, i))
        mx = Application.Max(Rng)   'maximum value in range rng
        
        For Each cell In Rng
            If Abs(zero - cell) < mx Then
                mx = Abs(zero - cell)
                phase = cell.Address
            End If
        Next cell
        
    Range(phase).Offset(, 1) = "x"
    
Next i
End Sub

With the data sample I gave you the code won't even start. If you change fx A2 from -2 to 2 then the code will start.

Actually, the code not only starts with your posted data, but since the mx value is -1 it simply loops through all the cells w/o changing that value. In other words:

Abs(zero - cell) < mx is NEVER True, so phase remains at its default value of "" (a zero-length string), and the line:

Range(phase).Offset(, 1) = "x"

causes a runtime error since Range("") is not a valid range object.

You need to revise your code. If your cells can hold negative values and the initial mx value is negative (as in you posted data set) then you must recognize that Abs(zero - cell) < mx is NEVER True b/c Abs(anything) is always greater than or equal to 0 (it can never be less than a negative value).