IsBlank in VBA to return formula

[clemons171]

I would like to do the following in VBA:

Have any cell within a range of cells to return a formula in the cell if the cell is blank.

Example:

1. If Cell A1 is Blank then Cell A1 returns this formula "=IF(H24="N",$F24," ")"
2. If I input data into Cell A1, it will do nothing...the data I input remains.
3. However, if a couple days later I go back in and delete that data in Cell A1, then the formula "=IF(H24="N",$F24," ")" is returned back to Cell A1.

If somebody could help me with this that would be wonderful!

 

[Rick Rothstein]

You hint at this being a cell within a larger range, but you didn't tell us what that range is nor did you tell what the formula should be for those other cells. Given that, I'll simply give you the answer to the exact question you asked. Below is event code that will do what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "A1" Then
    If Target.Value = "" Then Target.Formula = "=IF(H24=""N"",$F24,"" "")"
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[clemons171]

Understood. It was really vague. Let me try again:

I have a range of cells in a column. Lets call this range "Column1"
I have another range of cells in a different column . This range is named "Column2"
I have another range of cells in a different column . This range is named "Column3"

Here is what I would like:

If any cell in "Column1" is blank then I need it to have this formula: If any cell in "Column2" has the value "N" then return the value from the cell in "Column3"(in that respective row) to the cell in "Column1"

Hope this makes sense

 

[Rick Rothstein]

Are Column1, Column2 and Column3 Defined Names?

Are Column1, Column2 and Column3 full columns (Row 1 to Row 1048576?

Do the ranges Column1, Column2 and Column3 have the same number of cells in them?

Is there anyway you can give addresses for those ranges? I am interested in their actual column designations and starting row (the code will figure out where they end).

 

[clemons171]

Are Column1, Column2 and Column3 Defined Names? Yes

Are Column1, Column2 and Column3 full columns (Row 1 to Row 1048576? No, approx 100 cells

Do the ranges Column1, Column2 and Column3 have the same number of cells in them? Yes

Is there anyway you can give addresses for those ranges? I am interested in their actual column designations and starting row (the code will figure out where they end).

Answers above in quote

Column 1 - I24:I110
Column 2 - H24:H110
Column 3 - F24:F110

 

[Rick Rothstein]

Okay, replace the event code procedure I gave you earlier with this one...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Not Intersect(Target, Range("A24:A110")) Is Nothing Then
    If Target.Value = "" Then Target.FormulaR1C1 = "=IF(RC[7]=""N"",RC6,"" "")"
  End If
End Sub

 

[clemons171]

Works!!! Thank you! One last thing. If I delete the value out of one cell within Range("A24:A110"), it will return the formula, however, if I select multiple cells within Range("A24:A110") and hit the delete key, it does not return the formula in any of those cells. Is there anything I can do to remedy this?

Again, I really Appreciate the Help!

 

[Rick Rothstein]

Replace the code I just gave you with this...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Range("A24:A110")) Is Nothing Then
    For Each Cell In Intersect(Target, Range("A24:A110"))
      If Cell.Value = "" Then Cell.FormulaR1C1 = "=IF(RC[7]=""N"",RC6,"" "")"
    Next
  End If
End Sub

 

[clemons171]

WONDERFUL! THANK YOU