CODEWITHSUNDEEP
excel
Ryan Coleman
Ryan Coleman

Reputation: 39

How to only run a formula if the cell is blank

I am writing a formula to run a matching analysis on some elements of a spreadsheet. The column containing the formula already has values filled in, but I need to run the formula on the blank cells. The formula is working but overwriting the current info I have within the filled cells.

I have tried a VBA code which got too complicated and did not work. With VBA I tried inserting the formula into only the blank spaces but was having some issues. I have also tried a basic excel formula.

=IF(COUNTIFS(A:A,A2,BU:BU,"CLOUD")=0,"Not Cloud",IF(COUNTIFS(A:A,A2,BU:BU,"NOT CLOUD")=0,"Cloud","Hybrid"))

VBA Code I tried:

If (IsEmpty(Bucket) Or Bucket = "" Or Bucket = vbNullString) And _
 (GetCellValue(.Cells(i, "BU")) = "CLOUD") Then
    Range("CC:CC").Formula =  "=IF(COUNTIFS(A:A,A2,BU:BU,""CLOUD"")=0,"Not Cloud", _
    IF(COUNTIFS(A:A,A2,BU:BU,"NOT CLOUD")=0,"Cloud"","Hybrid"))"

A reminder this is a high-level version

I want that formula to only run in the cells that do not have a value already in place. It should not overwrite the data from the VBA script that I previously ran before doing the formula. The disposition column should be completely full of values. The formula needs to run in only the blank cells.High-Level Picture example of excel spreadsheet

Upvotes: 1

Views: 1309

Answers (1)

riskypenguin
riskypenguin

Reputation: 2199

You could achieve this with an additional column.

In the column to the right of Disposition (insert one if necessary) write the following formula (example for row 2, assuming Disposition is Column C):

=IF(C2=""; [Your formula here]; C2)

This will provide you with a column full of values, anytime you put a value into column C it will override the formula.

Edit: You can then copy this formula down the entire column to achieve the desired effect.

Upvotes: 1

Related Questions