replace cell with above value if not empty

hello friends ,
i have series of numbers in column a
i would like to replace "#value" with above value if not blank cell , with change in value of -4
if above cell is empty then it takes below cell values and minus -4

Try this:

Option Explicit

Sub blaaa()
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 2 To lr
    If IsError(Range("A" & i)) And Range("A" & i - 1) <> "" Then
    Range("A" & i) = Range("A" & i - 1).Value - 4
    ElseIf IsError(Range("A" & i)) And Range("A" & i - 1) = "" Then
    Range("A" & i) = Range("A" & i + 1).Value - 4
    End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "complete"

End Sub

What is the error message? Does your data look exactly as you have shown in your example? The code worked for me based upon the example presented. If your data sheet is different then I suggest you post a copy of your data sheet (upload) so that the code can be tweaked. It is difficult for a doctor to diagnose a patients issues if he cannot examine the patient. Same theory applies here.

i just took different values,

#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
-18

In your current example, you start with #value. Since there is no value directly above or beneath it, what should the new value be? This example does not fall into the criteria you asked for in your first post.

Attachment 364028


sorry alansidman , for inconvience ,

i have attach sample file ...

also ,

if order is in

-18
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

its works perfectly

but if order is in
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
-18

it doesnt replace with lower cell values ,
can we do something about this too ?

In your original post you indicated that if after a break, then the macro should look to the next cell below and subtract 4. Your current scenario does not allow this as it is also and error record. What should your criteria be if this is the case? Obviously, you cannot subtract 4 from a #value. Please elaborate on your criteria.

Maybe ...

Sub rs()
  With Range("A:A")
    .SpecialCells(xlCellTypeConstants, xlErrors).FormulaR1C1 = "=IF(R[-1]C <> """", R[-1]C, R[1]C) + 4"
    .SpecialCells(xlCellTypeFormulas, xlErrors).FormulaR1C1 = "=IF(R[-1]C <> """", R[-1]C, R[1]C) + 4"
    .Value = .Value
  End With
End Sub

See if this works for you:

Option Explicit


Sub blaaa()
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim i As Long
    Application.ScreenUpdating = False
    For i = lr To 2 Step -1
    If IsError(Range("A" & i)) And IsNumeric(Range("A" & i - 1)) Then
    Range("A" & i) = Range("A" & i - 1).Value - 4
    ElseIf IsError(Range("A" & i)) And Not IsNumeric(Range("A" & i - 1)) Then
    Range("A" & i) = Range("A" & i + 1).Value - 4
    End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "complete"

End Sub

thanks alansidman, its working fine , but only one small fix needed ,

I'm out of ideas on how to make this work. Will seek additional help from others in the forum.

ok sir , thanks for your efforts.

but please go through one last time to file attach ,

so you will idea of what i am trying to do ,

its my mistake , i dint posted question properly for answer i expected.

Attachment 364175

Hi,

Try this code for your sample file at post #14 :

Sub FillThem()
  Dim rng As Range, cell As Range
  Set rng = Range("F3:F47")
  'Downward processing
  For i = 1 To rng.Rows.Count
      Set cell = rng.Cells(i, 1)
      If IsError(cell) Then
         If Not IsError(cell.Offset(-1)) And Not IsEmpty(cell.Offset(-1)) Then
            cell.Value = cell.Offset(-1).Value - 5
         End If
      End If
  Next i
  'Upward processing
  For i = rng.Rows.Count To 1 Step -1
      Set cell = rng.Cells(i, 1)
      If IsError(cell) Then
         If Not IsError(cell.Offset(1)) And Not IsEmpty(cell.Offset(1)) Then
            cell.Value = cell.Offset(1).Value + 5
         End If
      End If
  Next i
End Sub

Regards