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]

You need to use """" not "" in the formula ie 4 quotes not 2.

 

[My Aswer Is This]

What do you want the formula to do?

And what does this mean
"I want to run this macro automatically in every 1 second."

You want the same script to keep running every second from now to eternity?

 

[Caleeco]

You need to use """" not "" in the formula ie 4 quotes not 2.

You could also you 'vbnullstring instead'... I believe it uses less system memory than ""

If cell.value <> vbNullString Then
        ' Do Something
End If

More details about this (and other methods) can be found here:
Optimize string handling in Visual Basic 6.0 - Part I

 

[steve the fish]

No you have misunderstood. I mean in the formula.

=IF(ISTEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE)),"", etc etc

needs to be

=IF(ISTEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE)),"""", etc etc

 

[Caleeco]

No you have misunderstood. I mean in the formula.

=IF(ISTEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE)),"", etc etc

needs to be

=IF(ISTEXT(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4),TRUE)),"""", etc etc

ah right, sorry my mistake - I thought you were referring to the IF statement.

 

[xunda_gunda]

Thank you all for your responses.

What do you want the formula to do?

And what does this mean
"I want to run this macro automatically in every 1 second."

You want the same script to keep running every second from now to eternity?

Yes, that's what I meant. While file is open.


Correcting quotes helped.
Code runs without any error, but it actually does another work.

In a range I have 3 kind of cells: with formulas, with values and empty cells.
I want code that will check if cell is empty and paste formula. Cells with values shouldn't be changed.

This code deleted values and paste formulas instead, but empty cells are still empty.
I think I have wrong logic in code.
Sorry for my English, I hope i explained problem clearly.

 

[steve the fish]

You have

If cell.Value <> "" Then

you need

If cell.Value = "" Then

 

[xunda_gunda]

You have

If cell.Value <> "" Then

you need

If cell.Value = "" Then

ouch :|

Thank you.

One more request:

I want this script to keep running every X second while file is open

 

[xunda_gunda]

I have this script in "ThisWorkbook"

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

and this one in Modules:

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
           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)),""""))))"
        End If
    Next
End Sub

It only works when I open workbook. But I want macro (Fill_Empty_Cells_with_Formulas) to run automatically in every 1 second after opening workbook.

Any help?
Pls