# VBA - finding a cell closest to a specific value

#### Gnera

##### New Member
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)
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

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### JoeMo

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

#### Gnera

##### New Member
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)
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
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

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

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,184
Messages
5,857,830
Members
431,900
Latest member

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

### Which adblocker are you using?

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

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