VBA to compare value to a range and place in adjacent column

I have a table with assessment data showing months of development for a client at different assessment times in Cells I2 to I12, with the number of months separating each assessment in Cells C2:C12. I have a range of Cells A14:A77, with values for months of development (1-60). What I want is to look at the first assessment value (Cell I2) and loop through A14-A77 to find the cell with the last value that is less than or equal to I2, place the value from I2 into column B in the cell adjacent to the last value in the A14-A77 range less than or equal to I2, then I want to place the remaining values in the I3-I12 range in column B offset down a number or rows equal to the number of months between assessments (values in C3:C12). I want this automated so that it will not have to be manually completed at each assessment time.

Any suggestions?

This will find the value less than or equal to I2 in column A, but i am not sure what you want for the last part

Please Login or Register  to view this content.

*double post*

Thanks, that solved the first part. Now what I have are another 9 assessment scores in I3 to I11. In C3 to C11 I have the number of months since the last assessment. What I want to do now is take the next assessment score and place it in column B a number or rows beneath the first value that was placed in column B by the first bit of code. The number or rows down to place the value is the value in column C. In the example I'm working with I've completed 6 assessments for a client. At the first assessemnt the results showed 21.9 months of development so the code you provided has placed that value in cell B34 next to the value 21 in A34. The next assessment was completed 8 months later (value in C3) and the client showed 25.6 months of development (value in I3). I need that value placed 8 rows beneath the first assessment results that are now in B34 (so placed in B42). The next assessment was done 6 months later (C4) and she scored at 29.5 months, so I need that value placed 6 rows down in column B (B48) and so on for the remaining assessments completed. If an an assessment has yet to be completed there will be a value of "NA" in either column C or I and I would need the sub routine to end if it encounters a value of "NA".

thanks so much

ok try this

Please Login or Register  to view this content.

Oh i didn't read the NA part... hang on

Please Login or Register  to view this content.

Haven't tried it to see if it worked but it certainly looks more elequent than what I was able to figure out on my own. My own code is working but I may give your's a look. I've moved the code to the activate routine in the worksheet so it will update each time you go to the worksheet.

Private Sub Worksheet_Activate()
Dim x As Integer
Dim r As Range
Dim r2 As Range
Dim r3 As Range
Set r = Range("I2")
Set r2 = Range("A14")
Set r3 = Range("B14:B300")

r3.ClearContents



Do Until r2.Row = 78 Or r2.Offset(1, 0).Value > r
If r2.Value <= r.Value Then
Set r2 = r2.Offset(1, 0)
End If
If r2.Value > r Then
Set r2 = r2.Offset(-1, 0)
End If
Loop
Set r2 = r2.Offset(0, 1)
r2.Value = r.Value

Dim n As Integer
Dim m As Integer
Dim r4 As Range
Dim r5 As Range
For n = 3 To 11
Set r4 = Range("I" & n)
Set r5 = Range("C" & n)
If r4.Value = "NA" Then
Exit For
End If
m = r5.Value
Set r2 = r2.Offset(m, 0)
r2.Value = r4.Value
Next n