616.00 -first value
-1 empty
-2 empty
616.00- last value
791.00
791.00
-1 empty
-2 empty
994.00

1,008.00

-
-

--

910.00
882.00
-1 empty
826.00
756.00

can anyone show me a code to interpolate this
replacing the blank space that is not more than 4 to tha average of
the first and last value?

thank you.

## Recommended Answers

``````Do While interval < number_of_loops

If IsEmpty(interpo.ActiveSheet.Range("E" & ctr)) = True Then
blank_counter = blank_counter + 1
If IsEmpty(interpo.ActiveSheet.Range("E" & ctr + 1)) = True Then
GoTo next_ctr
ElseIf Not IsEmpty(interpo.ActiveSheet.Range("E" & ctr + 1)) = True Then
f_value = interpo.ActiveSheet.Range("E" & ctr - blank_counter).Value
l_value = …``````

## All 4 Replies

I've tried this before. its so easy.

can i have your code. Please , i'm using it for my project.

please thank you,

``````Do While interval < number_of_loops

If IsEmpty(interpo.ActiveSheet.Range("E" & ctr)) = True Then
blank_counter = blank_counter + 1
If IsEmpty(interpo.ActiveSheet.Range("E" & ctr + 1)) = True Then
GoTo next_ctr
ElseIf Not IsEmpty(interpo.ActiveSheet.Range("E" & ctr + 1)) = True Then
f_value = interpo.ActiveSheet.Range("E" & ctr - blank_counter).Value
l_value = interpo.ActiveSheet.Range("E" & ctr + 1).Value

If blank_counter <= 4 Then
Do While blank_counter > 0
interpo.ActiveSheet.Range("E" & ctr + 1 - blank_counter).Value = (f_value + l_value) / 2
blank_counter = blank_counter - 1
Loop
End If

blank_counter = 0
GoTo next_ctr
End If
End If

next_ctr:
'MsgBox ctr
interval = interval + 1
ctr = ctr + 1
Loop
``````

this should do it for you

thank you a lot.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.