Insert formula if blank cell

[mjtolent]

Hi All,

Trying to right some code that will do the following:

1. Check a selected column range for blank cells
2. If any cell is blank insert a formula that uses figures in the 2 adjacent columns on the same row.
3. Move to next blank cells until complete.

I've tried a couple of things, but getting nowhere, thanks in advance
mj

 

[VBE313]

Assuming you are in Sheet1 using Column A.

Sub test()
Dim cl As Object, lastRow As Long
With Sheets("Sheet1")
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For Each cl In .Range("A1:A" & lastRow)
        If cl.Value = "" Then cl.Value = cl.Offset(0, 1).Value & " " & cl.Offset(0, 2).Value
    Next cl
End With
End Sub

 

[Fluff]

What column do you want to check & what is the formula?

 

[mjtolent]

Fluff,
Example, I want to check a selection of cells in column E and if blank divide column F by G from same row

 

[Fluff]

Do you want the code to run on all of col E or just cells that you have selected?
If cells you have select, will it always be a contiguous range of cells?

 

[mjtolent]

The data in column E will be of varying lengths dependent on certain criteria. I've used vba to make the selection, but need it to check the selected range for blanks and insert the formula to each blank cell in the selection only

 

[mjtolent]

Assuming you are in Sheet1 using Column A.

Sub test()
Dim cl As Object, lastRow As Long
With Sheets("Sheet1")
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For Each cl In .Range("A1:A" & lastRow)
        If cl.Value = "" Then cl.Value = cl.Offset(0, 1).Value & " " & cl.Offset(0, 2).Value
    Next cl
End With
End Sub

Thanks for taking the time to offer a solution. Will test and let you know if successful

 

[Fluff]

Ok, how about

Sub mjtolent()
   With Range("E2:E" & Range("G" & Rows.Count).End(xlUp).Row)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=rc[1]/rc[2]"
      .Value = .Value
   End With
End Sub

This will work for all blanks in col E, remove the line in blue if you want to keep the formula.