VBA - finding a cell closest to a specific value

Gnera

New Member
Joined
Nov 19, 2017
Messages
11
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
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!
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,853
Office Version
2010
Platform
Windows
Please post all the code so we can see how the variables: row, i, j and zero are defined.
 

Gnera

New Member
Joined
Nov 19, 2017
Messages
11
Full code

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

MrExcel MVP
Joined
May 26, 2009
Messages
16,853
Office Version
2010
Platform
Windows
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

New Member
Joined
Nov 19, 2017
Messages
11
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.
 
Last edited:

Gnera

New Member
Joined
Nov 19, 2017
Messages
11
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,853
Office Version
2010
Platform
Windows
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

New Member
Joined
Nov 19, 2017
Messages
11
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,853
Office Version
2010
Platform
Windows
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

New Member
Joined
Nov 19, 2017
Messages
11
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
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,699
Messages
5,470,274
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top