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




Reply With Quote