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]

Please post all the code so we can see how the variables: row, i, j and zero are defined.

 

[Gnera]

Full code

Dim row                             'first row of range [I]rng[/I]
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

row = 35

For i = 6 To 310 Step 8             'if i=6 --> column F
j = Cells(row, i).End(xlDown).row   'find the last used cell in a column to get the range lenght

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
 [COLOR=#333333]Next i[/COLOR]

 

[JoeMo]

In your OP you said: "When the closest value is in the first cell, it doesn't mark the cell and the results of the calculations are wrong."

Which cell do you regard as the first cell?

 

[Gnera]

I have defined my range rng in the code: rng = Range(Cells(row, i), Cells(j, i)). So the first cell is (row, i). I've put row to equal 35 and i changes from 6 (i.e. F) to 310 (i.e. KX). So the first cell changes through looping and is first F35, then N35, then V35 and so on until KX35. By now I've discovered that whether the code works like it should (can detect also the first cell when it's closest to 0) depends on the first two cells of the range. So for the first loop - when F35 and F36 are both positive or F35 is negative and F36 positive then the code works. But when F35 and both F36 are negative or F35 is positive and F36 negative then it doesn't detect F35 when it's the one closest to 0.

 

[Gnera]

Edit: the code starts when F35 is not the closest value in the first loop (for next loops the first cell (N35, V35,...), can be the closest value but then the code just doesn't mark them). But when F35 is the closest and is positive and F36 is negative then the code doesn't even start but gives run-time error '1004': "Method 'Range' of object '_Global' failed" and debugging highlights the line Range(phase).Offset(, 1) = "x"
When both F35 and F36 are negative, then it gives the same error and in this case F35 or F36 don't even have to be the closest values.

 

[JoeMo]

Hard to tell what's going on w/o some sample data to test. Have you stepped through the code using the F8 key? For example, what's the value of phase when the debugger highlights Range(phase).Offset(,1) = "x"?

 

[Gnera]

I got no information from stepping through. I've uploaded an example on how the code works (or doesn't). Maybe you could check it, I myself am lost. The example is here: https://ufile.io/16s5o

 

[JoeMo]

Sorry, I don't download from the web. Can you post a sample of your data where the first value is the one that's being missed? Also, can you tell us what's the value of phase when the debugger highlights Range(phase).Offset(,1) = "x"?

 

[Gnera]

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.