Write a macro to populate a column with a formula
 

I suspect this is an elementary concept for VBA, hopefully this will b
an easy answer for y'all:
I would like to write a macro that populates a cell with a formul
given a certain condition is met in another cell. I setup a pivot tabl
that imports pertinent data from a master table. One column contain
the trigger info - an "M" means that we do not want the formula to b
imported otherwise we do want it. the column containing the trigge
will not move, likewise the destination formula will not move (no fanc
offsets required). I simply want the macro to recognize a non-"M" i
cell D4 and put my formula in F4.

The pivot table will constantly be updating, the "M"s and othe
characters (always integers if not M) in the target column (D) will b
changing. When an M shows up, the spreadsheet user will have t
manually enter data into the F column, which would overwrite a stati
entry if we were to simply write a formula.

I am not quite sure how to make the leap to scanning all the rows of m
document. I changed each of the ranges to (D4:D10), (F4:F10
respectively but when I run the macro I get the error "type mismatch"


Here's my code:
--------------------------------------------------------------------------------
Sub y_1()
If Range("D4:D10").Value < "M" Then
Range("F4:F10").Formula = "=D4+1"
Else
Range("F4:F10").Value = ""
End If
End Sub
--------------------------------------------------------------------------------



Thank you in advance for any help you might be able to give

--
Message posted from http://www.ExcelForum.com

Write a macro to populate a column with a formula
 

Hi
try the following
sub insert_formula
dim rng as range
dim cell as range
set rng=activesheet.range("D4:D10)
for each cell in rng
if rng.value="M" then
rng.offset(0,2).value=""
else
rng.offset(0,2).formulaR1C1="=R[0]C[-2]+1"
end if
next
end sub

--
Regards
Frank Kabel
Frankfurt, Germany


I suspect this is an elementary concept for VBA, hopefully this will
be an easy answer for y'all:
I would like to write a macro that populates a cell with a formula
given a certain condition is met in another cell. I setup a pivot
table that imports pertinent data from a master table. One column
contains the trigger info - an "M" means that we do not want the
formula to be imported otherwise we do want it. the column containing
the trigger will not move, likewise the destination formula will not
move (no fancy offsets required). I simply want the macro to
recognize a non-"M" in cell D4 and put my formula in F4.

The pivot table will constantly be updating, the "M"s and other
characters (always integers if not M) in the target column (D) will
be
changing. When an M shows up, the spreadsheet user will have to
manually enter data into the F column, which would overwrite a static
entry if we were to simply write a formula.

I am not quite sure how to make the leap to scanning all the rows of
my document. I changed each of the ranges to (D4:D10), (F4:F10)
respectively but when I run the macro I get the error "type
mismatch".


Here's my code:
---------------------------------------------------------------------
-----------
Sub y_1()
If Range("D4:D10").Value < "M" Then
Range("F4:F10").Formula = "=D4+1"
Else
Range("F4:F10").Value = ""
End If
End Sub
---------------------------------------------------------------------
-----------



Thank you in advance for any help you might be able to give!


---
Message posted from http://www.ExcelForum.com/

Write a macro to populate a column with a formula
 

Here are two solutions that work somewhat differently.
The first is more like your original attempt - it sets
the values at the time the macro is run:

Range("F4:F10").Formula = "=D4+1"
For Each Cell In Range("D4:D10").Cells
If Cell.Value = "M" Then
Cells(Cell.Row, "F").Value = ""
End If
Next

The second puts the condition in the formula for each
cell - so it's more dynamic. (Actually - you probably
wouldn't need to use VB at all for this - just put the
formula below - strip out the extra quotes - in the first
row and copy it to the rest.)
Range("F4:F10").Formula = "=IF(D4=""M"","""",D4+1)"

HTH,
Ken

-----Original Message-----
I suspect this is an elementary concept for VBA,
hopefully this will be
an easy answer for y'all:
I would like to write a macro that populates a cell with
a formula
given a certain condition is met in another cell. I
setup a pivot table
that imports pertinent data from a master table. One
column contains
the trigger info - an "M" means that we do not want the
formula to be
imported otherwise we do want it. the column containing
the trigger
will not move, likewise the destination formula will not
move (no fancy
offsets required). I simply want the macro to recognize
a non-"M" in
cell D4 and put my formula in F4.

The pivot table will constantly be updating, the "M"s
and other
characters (always integers if not M) in the target
column (D) will be
changing. When an M shows up, the spreadsheet user will
have to
manually enter data into the F column, which would
overwrite a static
entry if we were to simply write a formula.

I am not quite sure how to make the leap to scanning all
the rows of my
document. I changed each of the ranges to (D4:D10),
(F4:F10)
respectively but when I run the macro I get the
error "type mismatch".


Here's my code:
---------------------------------------------------------
-----------------------
Sub y_1()
If Range("D4:D10").Value < "M" Then
Range("F4:F10").Formula = "=D4+1"
Else
Range("F4:F10").Value = ""
End If
End Sub
---------------------------------------------------------
-----------------------



Thank you in advance for any help you might be able to
give!


---
Message posted from http://www.ExcelForum.com/

.