VBA for finding a value in a column from a set target value

[earendil]

Target	Percent	Value	Answer
0.9	1	20	? = Average between values for 0.91 & 0.87
	0.91	40
	0.87	80
	0.81	10

<TBODY>
</TBODY>

Im looking to put in a value in a target cell and wanting to search for that target value in a column. Never falls directly on target value so need to take average of the greater and less than value and output that to answer cell.

 

[b.downey]

Here is some quick dode that solves the example...

Sub Process()
    Dim Wb As Workbook
    Dim trg As Single
    Dim RowNo As Long
    
    Dim Ws As Worksheet
    
    Set Wb = ThisWorkbook
    Set Ws = Wb.Worksheets("Sheet1")
    
    trg = Ws.Range("A2")
    
    For RowNo = 2 To 5
        
        If Ws.Cells(RowNo, "B") < trg Then
            Ws.Cells(2, "D") = (Ws.Cells(RowNo - 1, "B") + Ws.Cells(RowNo, "B")) / 2
            Exit Sub
        End If
    Next
End Sub