Adding values

[mikke3141]

Could you please help. Why the code below gives an error 13.

Sub add_offsets()

Dim rng As Range
 
With Range("F1:F34")
    Set rng = .SpecialCells(xlCellTypeBlanks)
    If Not rng Is Nothing Then rng.Value = rng.Offset(, -4).Value + rng.Offset(, -3).Value
End With

End Sub

Thank you for your help.

[mbarron]

The macro will work if blanks rows are not consecutive. It will not work, for example, if rows 3 and 4 are blank, but wold work if 3 and 5 are blank. You'll have to loop through the range - something like this:
[VBA]
Sub add_offsets2()
Dim cCell As Range
Dim rng As Range
Set rng = Range("F1:F34")
For Each cCell In rng
If cCell.Value = "" Then
cCell.Value = cCell.Offset(, -4).Value + cCell.Offset(, -3).Value
End If
Next
End Sub[/VBA]

[mikke3141]

Hello and thank you for your comment. I do not understand why it is so. The code below works, but adding two values doesn't.

Sub add_offsets()
 
Dim rng As Range
 
With Range("F1:F34")
    Set rng = .SpecialCells(xlCellTypeBlanks)
    If Not rng Is Nothing Then rng.Value = rng.Offset(, -4).Value
End With
 
End Sub

[mdmackillop]

Your code does not work consistently. Put 1-34 in each column and create some blank blocks in Column F

BTW, your code will error if there are no blank cells. You need
[VBA]
With Range("F1:F34")
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeBlanks)
If Not rng Is Nothing Then
rng.Value = rng.Offset(, -4).Value
End If
On Error GoTo 0
End With

[/VBA]

[mbarron]

The code you just posted will set the values of the blank cells to the first blank's corresponding value. By that I mean if row F3 is the first blank value, all blank values will receive the value in B3, not the value that is in the same row as the blank cell(s). Using this [vba] If Not rng Is Nothing Then rng.Value = rng.Offset(, -4).Value + rng.Offset(, -3).Value
[/vba] Will work if the blank rows are not consecutive. If cells F1, F10 and F7 are blank, all three would receive the value of B1 + C1. If Cells F11 and F12 are blank, you receive the Type Mismatch Error. (Error 13). You are now adding two arrays (I think).

[mbarron]

After more testing ---
If the your first blank does not have a blank in the next row but there are consecutive blanks farther down in the row, such as blanks being in F3, F11 and F12, all blanks would receive the value in B3 + C3. If your first blank does have a consecutive blank row, such as F2, F3, and F13 the type Mismatch occurs.