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!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Please post all the code so we can see how the variables: row, i, j and zero are defined.
 
Upvote 0
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]
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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"?
 
Upvote 0
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"?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top