Using a macro to populate a cell with a formula

[Steve]

Hi

I would like to populate column E with a formula (column C
+ column D) where the the value of Column A is null.

Is this possible ?

Thank you in advance
Steve

 

[Tom Ogilvy]

Dim rng as Range
set rng = Intersect(ActiveSheet.UsedRange,Columns(1))
rng.offset(0,4).Formula = "=if(A1="""",C1+D1,"""")"

 

[Bernie Deitrick]

Steve,

The most simple solution is to include the null condition in the formula:

Sub Steve()
Intersect(Range("E:E"), ActiveSheet.UsedRange).FormulaR1C1 = _
"=IF(RC[-4]="""",RC[-2]+RC[-1],"""")"
End Sub

HTH,
Bernie
MS Excel MVP

 

[Steve]

Hi Tom

Thanks for that.

I have one problem remaining:

Your macro creates a brand new column in column E. As a
result it overwrites other information that is already in
that column (on other rows. i.e. the rows where A is not
null). Is it possible to populate Column A but still keep
any keep values that were already there before the macro
is run ?

Thanks
Steve

 

[Tom Ogilvy]

Dim rng as Range, sForm as String
Dim sStr as String
set rng = Intersect(ActiveSheet.UsedRange, _
Columns(5)).specialCells(xlBlanks)
sStr = rng(1).row
sForm = "=if(A" & sStr & "="""",C" & sStr _
& "+D" & sStr & ","""")"
rng.Formula = sForm

--
Regards,
Tom Ogilvy

 

[Guest]

Hi Bernie

This is similar to what Tom suggested, however, I still
get a problem in so far as it completely overwrites other
data that was already in column E (i.e. where column A was
not null). Is there anyway around this ?

Thanks

-----Original Message-----
Steve,

The most simple solution is to include the null condition in the formula:

Sub Steve()
Intersect(Range("E:E"),

ActiveSheet.UsedRange).FormulaR1C1 = _

"=IF(RC[-4]="""",RC[-2]+RC[-1],"""")"
End Sub

HTH,
Bernie
MS Excel MVP

Hi

I would like to populate column E with a formula (column C
+ column D) where the the value of Column A is null.

Is this possible ?

Thank you in advance
Steve

.

 

[Steve]

Tom

I am sorry to be a pain but i get the message "Run Time
Error. No Cells were found"

Steve

 

[Bernie Deitrick]

Steve,

First, I want to apologize to Tom for jumping in - my newsreader showed your
post as not being answered when I first replied.

And I'm not sure exactly what you want, so try these macros: The first fills
in the blanks of column E, and the second and third fill in column E where A
is blank, with different formulas.

Sub Steve2()
Intersect(Range("E:E"), ActiveSheet.UsedRange). _
SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IF(RC[-4]="""",RC[-2]+RC[-1],"""")"
End Sub

Sub Steve3()
Intersect(Range("A:A"), ActiveSheet.UsedRange). _
SpecialCells(xlCellTypeBlanks).Offset(0, 4).FormulaR1C1 = _
"=RC[-2]+RC[-1]"
End Sub

Sub Steve4()
Intersect(Range("A:A"), ActiveSheet.UsedRange). _
SpecialCells(xlCellTypeBlanks).Offset(0, 4).FormulaR1C1 = _
"=RC[-2]+RC[-1]"
End Sub

HTH,
Bernie
MS Excel MVP

Hi Bernie

This is similar to what Tom suggested, however, I still
get a problem in so far as it completely overwrites other
data that was already in column E (i.e. where column A was
not null). Is there anyway around this ?

Thanks

-----Original Message-----
Steve,

The most simple solution is to include the null condition in the formula:

Sub Steve()
Intersect(Range("E:E"),

ActiveSheet.UsedRange).FormulaR1C1 = _

"=IF(RC[-4]="""",RC[-2]+RC[-1],"""")"
End Sub

HTH,
Bernie
MS Excel MVP

Hi

I would like to populate column E with a formula (column C
+ column D) where the the value of Column A is null.

Is this possible ?

Thank you in advance
Steve

.

 

[Tom Ogilvy]

I assumed column 5 was where there were blanks If you want to do E based on
blanks in A.

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(5))
sStr = rng(1).row
sForm = "=if(A" & sStr & "="""",C" & sStr _
& "+D" & sStr & ","""")"
rng.Formula = sForm

 

[David McRitchie]

I think you would be better off with a worksheet solution

E2: =IF(TRIM(A2)="",C2+D2, somethingelse)

However as a programming solution, it really would make
a difference if you can use SpecialCells and work from
blank cells in Column A

Sub xxxxxx()
Dim cell As Range, s As Double
For Each cell In Range("A:A").SpecialCells(xlCellTypeBlanks)
'Range("A:A").SpecialCells(xlBlanks)
s = 0
If IsNumeric(cell.Offset(0, 2)) Then s = cell.Offset(0, 2)
If IsNumeric(cell.Offset(0, 3)) Then s = s + cell.Offset(0, 3)
cell.Offset(0, 4) = s
Next cell
End Sub

If the cells in a could have spaces you would have to use TRIM

Dim cell As Range, s As Double
For Each cell In Intersect(Range("A:A"), ActiveSheet.UsedRange)
'Range("A:A").SpecialCells(xlBlanks)
if trim(cell) = '' then
s = 0
If IsNumeric(cell.Offset(0, 2)) Then s = cell.Offset(0, 2)
If IsNumeric(cell.Offset(0, 3)) Then s = s + cell.Offset(0, 3)
cell.Offset(0, 4) = s
end if
Next cell
End Sub

Both macro solutions above assume that if the cell in A is not empty then
leave the cell in column E alone, at least since you don't indicate
otherwise that how I read your question. And you later indicated to
leave the cell in E alone if not adding the cells.