Populating column N with a formula if column A is Null or Blank

[Steve]

Yesterday, I posted a message on here and received a
number of good responses from Bernie Deitrick and Tom
Ogilvy (Thank you to you both). However, due to the fact
that I probably didn't state what my problem was too
clearly (as i was trying to keep it simple) I still have
not yet achieved my ultimate result.

So today, I am going to try and be a little clearer to see
if I can achieve what I am setting out to do.

My spreadhsheet contains a number of columns.
Column A normally has an id in it. Where it does, column
N is populated with a value. This value is the difference
between two other columns (column G minus column C),
although this field is already populated before the data
gets into Excel (it is calculated in Access).

On a number of rows, column A is blank/null, however,
there is data in the other columns on the corresponding
row. What i want to do is write a formula in column N
that calculates the difference between column G and column
C WHERE column a is Null/Blank. Where column A is not
blank/null, I do not want to overwrite the value that is
already in the cell.

Thanks in advance

Steve

 

[Guest]

hi,
=if(a1="",g1-c1,a1)
if a1 ISNULL then subtrace c from g exlse value of a.
enter this in N.

 

[Tom Ogilvy]

Yesterday it was C, D, and E and addition, so yes, that wouldn't have
described this situation.

Dim rng as Range, sForm as String
Dim rng1 as Range
Dim sStr as String
On Error Resume Next
set rng1 = Intersect(Activesheet.UsedRange, _
Columns(1)).SpecialCells(xlBlanks)
On Error goto 0
if rng1 is nothing then
msgbox "No blank cells in column A"
exit sub
End if

set rng = Intersect(rng1.EntireRow, _
Columns(14))
sStr = rng(1).row
sForm = "=G" & sStr _
& "-C" & sStr
rng.Formula = sForm


is adjusted to do what you describe.

if you get the response that there are no blank cells in Column A, then it
is possible that the cells look blank but are not. Then try this code:

Dim sForm as String
Dim rng1 as Range, cell as Range
Dim sStr as String

set rng1 = InterSect(Activesheet.UsedRange, _
Columns(14)).Cells
set rng1 = rng1.offset(0,-13)
for each cell in rng1
if len(trim(cell.value)) = 0 then
sStr = cell.row
sForm = "=G" & sStr _
& "-C" & sStr
cell.offset(0,13).Formula = sForm
end if
Next

 

[Steve]

Tom

Many thanks for that.

It did say that there were no blank cells the first time
so i used the second bit of code and it worked fine. For
my information, why did I get that result with the first
bit of code ?

Finally, I now want to append this code to look at column
H in adiition to A. (i.e.if column A or H are blank ....

Any ideas?

Thanks once again
Steve

 

[Tom Ogilvy]

Dim sForm as String
Dim rng1 as Range, cell as Range
Dim sStr as String

set rng1 = InterSect(Activesheet.UsedRange, _
Columns(14)).Cells
set rng1 = rng1.offset(0,-13)
for each cell in rng1
' adjusted to check A and H
if len(trim(cell.value)) = 0 or _
len(trim(cell.offset(0,7)) = 0 then
sStr = cell.row
sForm = "=G" & sStr _
& "-C" & sStr
cell.offset(0,13).Formula = sForm
end if
Next

It is possible there is a null string in the cells or something similar,
since the data is imported.