Only update cells that are blank/empty

[EssKayKay]

Hello,

I have a VBA routine I received from a gracious member here (sorry, don’t remember who) that does mostly what I want. It populates a range (T33:T133) with a formula which appears to be working. However, if a cell in the range is already populated with a date I do not want the formula to overwrite what’s there (i.e., only update blank cells). I tried If ActiveCell.Value>0 then but could not get it to work. Any suggestion?

Here’s a portion of the VBA routine in question –

   Range("T33").Formula = "=IF(R33<=$U$27,1,"""")"
   Range("T33:T133").FormulaR1C1 = "=IF(RC[-2]<=R27C21,1,"""")"

Thanks for viewing,
Steve K.

 

[Micron]

If a cell has a formula but no result,
=""
is True. If it has a result, ="" is False. Use that to skip cells with a value.
EDIT - that may not be sufficient where a formula returns an error, or if no results, a zero

 

[Micron]

To avoid cells where a formula returns an error test for ISERROR. My answers are based on the assumption that you're looping through cells. If not, then I don't know how to apply these tests to a range of cells all at once.

 

[EssKayKay]

Here’s more of the code in including the call DistributePITI() which is where the above code exists.

This is a portion of the more complex routine:

If Range("F18") <= 0 Then
On Error Resume Next
Dim cell As Range
Dim rng As Range

' Check for entry made to column M
Set rng = Intersect(Target, Columns("M:M"))

' If not entry made in column M, exit
    If rng Is Nothing Then Exit Sub

' Loop through new entries made in column M
    For Each cell In rng

' Check month of date in column C
    If (cell.Offset(0, -10) <> "") And (Month(cell.Offset(0, -10)) = 12) Then
        Call DistributePITI
    End If
Next cell

Public Sub DistributePITI()

If Range("M33") > 0 Then
     If Year(Range("M32").End(xlDown).Offset(0, -10)) = _
         Year(Range("M32").End(xlDown).Offset(1, -10)) Then
         Range("U27") = Year(Range("M32").End(xlDown).Offset(0, -10)) - 1
     Else
         Range("U27") = Year(Range("M32").End(xlDown).Offset(0, -10))
     End If
End If

'.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .
  Range("T33").Formula = "=IF(R33<=$U$27,1,"""")"
  Range("T33:T133").FormulaR1C1 = "=IF(RC[-2]<=R27C21,1,"""")"
'.  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  .  . 

Dim DistDate As Range 
For Each DistDate In Range("T33:T133")
    If DistDate = "" Then DistDate.ClearContents
Next DistDate

'- - - - - - - - - - - - - - - - - - - - - - - - - -
   Range("T33").Select
   Range(Selection, Selection.End(xlDown)).Copy
   Range("T33").PasteSpecial Paste:=xlPasteValues
   Range("T32").Select

End Sub

I was hoping there was a way to only update the range with the following line in blank cells but maybe that is not possible. As you can tell, I am no programer.
Range("T33:T133").FormulaR1C1 = "=IF(RC[-2]<=R27C21,1,"""")"

Thanks again

 

[Joe4]

Try this:

    Range("T33:T133").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[-2]<=R27C21,1,"""")"

This should only put the formula in the blank cells of range T33:T133.

 

[Micron]

After reviewing I don't think I completely understand. The formula is already in the range, or your code will do that? You say that after that, you don't want to overwrite a date with that formula again. However, the date you see is the result of a formula no? Over writing the formula will leave you with the same date value unless you edit cells that the formula uses.

if a cell in the range is already populated with a date

If "the range" is T33:T133 I don't see a problem unless you change what's in these cells: RC[-2]&lt;=R27C2

 

[EssKayKay]

Thanks all - I'll have to look into this much closer. Apparently I'm missing something. So let's put this on hold for a bit. I'm sure I'll be back.

Sorry for being so vague,
Steve

 

[Joe4]

It is my understanding (which may not be correct), that the range T33:T133 has entries in some of the cells, and others are blank.
It sounds like you only want to place the formulas in the blank cells in that range.
If that is what you are actually trying to do, the code I gave you will do just that - it will only put the formula in the cells in that range that have nothing in them (no values or formulas).

 

[EssKayKay]

It is my understanding (which may not be correct), that the range T33:T133 has entries in some of the cells, and others are blank.
It sounds like you only want to place the formulas in the blank cells in that range.
If that is what you are actually trying to do, the code I gave you will do just that - it will only put the formula in the cells in that range that have nothing in them (no values or formulas).

Yup Joe, that's what I'm trying to do. However, I found another issue I'll have to fix first - then back to this guy. If I need more help (and I suspect that will happen), I'll be back.

Thank you again for your concern and help - it is appreciated,
Steve

 

[EssKayKay]

It is my understanding (which may not be correct), that the range T33:T133 has entries in some of the cells, and others are blank.
It sounds like you only want to place the formulas in the blank cells in that range.
If that is what you are actually trying to do, the code I gave you will do just that - it will only put the formula in the cells in that range that have nothing in them (no values or formulas).

Joe,
I finally got back to this project. I tried your routine. I have more testing to do but for right now this is working great. I most certainly appreciate your help.

Again, my sincere thanks,
Steve K.