Results 1 to 2 of 2

Thread: Find closest value in range

Threaded View

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2009
    Posts
    1

    Find closest value in range

    My code loops through lots of blocks of data that are separated with a 'd' (see attachment).

    The code loops through each block of data between the d's perfectly.

    But I can't get it to search each block - the code in red won't work no matter how I change it.

    What it should do is search the values in column D and find the one that's closest to the value in column N. Once it's done that, an X should appear in the same row in Column F - as per the bottom 2.

    My code is here:

    Code:
    Sub subd()
    
        Columns("F:N").Select
        Selection.ClearContents
      
        Dim rngFind As Range
        Dim strValueToPick As String
        Dim rngLook As Range
        Dim strFirstAddress As String
        Dim topRow As Long
        Dim botRow As Long
        Dim count As Integer
        Dim W As Integer
        
        Set rngLook = Range("B1:B" & Cells(Rows.count, "B").End(xlUp).Row)
        strValueToPick = "d"
        With rngLook
            Set rngFind = .Find(strValueToPick, LookIn:=xlValues, LookAt:=xlWhole)
            If Not rngFind Is Nothing Then
                strFirstAddress = rngFind.Address
                Do
                    topRow = rngFind.Row
                    Set rngFind = .FindNext(rngFind)
                    botRow = rngFind.Row - 1
                    If botRow - topRow > 1 Then
    
    count = botRow - topRow
    W = (count * (Range("D" & topRow + 1).Value)) / WorksheetFunction.Pi
    Range("D" & topRow + 1).Offset(0, 10).Value = W
    
    
    If Range("D" & topRow + 1).Offset(3, 0).Value < 20 Then
    
    ' +++++++++++++++++++++++++
    
        Dim d As Double, i As Long, j As Long
        Dim LR As Long, x
        d = 1000
        LR = botRow - topRow
        
        x = Range("D" & topRow + 1).Offset(0, 10).Value
    
        For i = 1 To LR + 1
            If Range("D" & i).Value = "" Then
                d = 1000
                x = Range("N" & i + 1).Value
                Range("f" & j).Value = "X"
            Else
                If Abs(Range("D" & i).Value - x) < d Then
                    j = i
                    d = Abs(Range("D" & i).Value - x)
                End If
            End If
        Next i
    
    ' +++++++++++++++++++++++++
    
    Else
    Range("D" & topRow + 1).Offset(2, 2).Value = "b1"
    End If
            End If
                Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
            End If
        End With
    
    End Sub
    Attached Images Attached Images  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width