VBA if cell is empty then...

[xunda_gunda]

Hello,

I want your help.
I want to use macro, that will search a blank cells in predetermined range and if cell is empty it should paste formula.
I want to run this macro automatically in every 1 second.

I searched procedures like this and tried to adjust to my excel, but something goes wrong.

Sub Check_Empty_Cells_in_Every_3_Seconds()
Application.OnTime Now + TimeValue("00:00:01"), "Fill_Empty_Cells_with_Formulas"
End Sub


Sub Fill_Empty_Cells_with_Formulas()
    Dim rng As Range
    Dim i As Long


    'Set the range in column A you want to loop through
    Set rng = Range("D15:D139")
    For Each cell In rng
        'test if cell is empty
        If cell.Value <> "" Then
            'write to adjacent cell
[COLOR=#ff0000]           cell.Offset(0, 0).Value = "=IF(ISTEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE)),"",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE)<12,"",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE)<=$D$8,"",IF(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE),-12,2)<>"",MIN(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE),-12,2),INDIRECT(ADDRESS(ROW()-1,COLUMN()+3,4),TRUE)),""))))"[/COLOR]
        End If
    Next
End Sub

Red part of code is not working:

Run-time error ‘1004’:
Application-defined or object defined error

What am I missing?

Thank you in advance.

 

[steve the fish]

Call the macro Fill_Empty_Cells_with_Formulas from the workbook open event. The ontime code should be at the end of the macro Fill_Empty_Cells_with_Formulas not within the open event.

 

[xunda_gunda]

Call the macro Fill_Empty_Cells_with_Formulas from the workbook open event. The ontime code should be at the end of the macro Fill_Empty_Cells_with_Formulas not within the open event.

Thank you very very very much

Thank you all guys

 

[evinx]

great code how can i edit this to add my formula ?

=ALS(L9="";"";M9*VERT.ZOEKEN(L9;'Database Freon'!B2:C20;2;0))

L = text
M = number
Database Freon B Column is text
Database Freon C column is number
formula is at Column N or other column but value to Column N

the formula calculates M * C = N

example of Sheet where code need to search

[TABLE="width: 302"]
<tbody>[TR]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]r22[/TD]
[TD]0.68[/TD]
[TD]1.16[/TD]
[/TR]
[TR]
[TD]R410A[/TD]
[TD]0.68[/TD]
[TD]1.42[/TD]
[/TR]
[TR]
[TD]r22[/TD]
[TD]1.30[/TD]
[TD]2.22[/TD]
[/TR]
[TR]
[TD]r22[/TD]
[TD]1.30[/TD]
[TD]2.22[/TD]
[/TR]
</tbody>[/TABLE]

example of sheet Database Freon

  [TABLE]
<tbody>[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl66"]C[/TD]
[/TR]
[TR]
[TD="class: xl65"]R410A[/TD]
[TD="class: xl66"]2.088[/TD]
[/TR]
[TR]
[TD="class: xl65"]R407C[/TD]
[TD="class: xl66"]1.774[/TD]
[/TR]
[TR]
[TD="class: xl65"]R22[/TD]
[TD="class: xl66"]1.710[/TD]
[/TR]
[TR]
[TD="class: xl65"]R32[/TD]
[TD="class: xl66"]0.675[/TD]
[/TR]
[TR]
[TD="class: xl65"]R134A[/TD]
[TD="class: xl66"]1.430[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 150"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

can someone help me please ?