Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to populate a cell with a formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to populate a cell with a formula
Dim rng as Range
set rng = Intersect(ActiveSheet.UsedRange,Columns(1)) rng.offset(0,4).Formula = "=if(A1="""",C1+D1,"""")" -- Regards, Tom Ogilvy "Steve" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to populate a cell with a formula
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to populate a cell with a formula
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 -----Original Message----- Dim rng as Range set rng = Intersect(ActiveSheet.UsedRange,Columns(1)) rng.offset(0,4).Formula = "=if(A1="""",C1+D1,"""")" -- Regards, Tom Ogilvy "Steve" wrote in message ... 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to populate a cell with a formula
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 "Tom Ogilvy" wrote in message .. . Dim rng as Range set rng = Intersect(ActiveSheet.UsedRange,Columns(1)) rng.offset(0,4).Formula = "=if(A1="""",C1+D1,"""")" -- Regards, Tom Ogilvy "Steve" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to populate a cell with a formula
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 "Steve" wrote in message ... 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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to populate a cell with a formula
Tom
I am sorry to be a pain but i get the message "Run Time Error. No Cells were found" Steve -----Original Message----- 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 "Tom Ogilvy" wrote in message . .. Dim rng as Range set rng = Intersect(ActiveSheet.UsedRange,Columns(1)) rng.offset(0,4).Formula = "=if(A1="""",C1+D1,"""")" -- Regards, Tom Ogilvy "Steve" wrote in message ... 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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to populate a cell with a formula
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 wrote in message ... 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 "Steve" wrote in message ... 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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to populate a cell with a formula
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 -- Regards, Tom Ogilvy "Steve" wrote in message ... Tom I am sorry to be a pain but i get the message "Run Time Error. No Cells were found" Steve -----Original Message----- 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 "Tom Ogilvy" wrote in message . .. Dim rng as Range set rng = Intersect(ActiveSheet.UsedRange,Columns(1)) rng.offset(0,4).Formula = "=if(A1="""",C1+D1,"""")" -- Regards, Tom Ogilvy "Steve" wrote in message ... 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 . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a macro to populate a cell with a formula
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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Steve" wrote in message ... Hi I would like to populate column E with a formula (column C + column D) where the value of Column A is null. Is this possible ? Thank you in advance Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
function to populate a cell where formula is NOT located | Excel Worksheet Functions | |||
Macro to populate same cell on multiple worksheets | Excel Discussion (Misc queries) | |||
How do I populate a worksheet name in a formula using a cell ref. | Excel Discussion (Misc queries) | |||
How to populate column with formula based on value in cell | Excel Worksheet Functions | |||
Write a macro to populate a column with a formula | Excel Programming |