Excel Help

[Tim Di Prinzio]

I was wondering if anyone could point me in the right direction on a few
questions I have. I would like to evaluate the value in a cell in a
column and if it is within a certain range write a value to a new
column. I would like to do this for and entire column? Meaning write the
function once and have it work for the whole column.

Also is it possible to have a formula change the value of a cell and
write the results back to the same cell. i.e n1 has a value of 25. the
value I really need is 25/2. This is on imported data. I prefer not to
write to another column. Basically as the data is imported or inputted
the calc takes place.

Thanks in advance for the help.

Regards,

Tim

 

[Ron Rosenfeld]

I was wondering if anyone could point me in the right direction on a few
questions I have. I would like to evaluate the value in a cell in a
column and if it is within a certain range write a value to a new
column. I would like to do this for and entire column? Meaning write the
function once and have it work for the whole column.

Place your formula in the new column; then fill down the required number of
rows.

Also is it possible to have a formula change the value of a cell and
write the results back to the same cell. i.e n1 has a value of 25. the
value I really need is 25/2. This is on imported data. I prefer not to
write to another column. Basically as the data is imported or inputted
the calc takes place.

It is not possible. Functions return values, they do not affect other cells.

There are other ways to do what you require, however. You could, for example,
place a number "2" in some blank cell. Edit/Copy that cell. Then select the
cells which need to be modified and Edit/Paste Special/Divide.

This can also be done using a VBA macro.

Thanks in advance for the help.

Regards,

Tim

--ron

 

[Bernie Deitrick]

Tim,

Get a formula to work in the column where you want the new values to appear, something along the
lines of

=IF(AND(A2>3,A2<5),"New Value","")

and then when you have it working, record a macro where you select that cell, press F2 to enter Edit
mode, and then press Enter to enter the formula. Your code will look like this, for the formula
above entered into cell C2:

Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]>3,RC[-2]<5),""New Value"","""")"
Range("C3").Select
Range("C3").Select


Then change the code to include the entire C column:

Range("C2:C" & Cells(Rows.Count, 2).End(xlUp).Row).FormulaR1C1 = _
"=IF(AND(RC[-2]>3,RC[-2]<5),""New Value"","""")"

This code will match column B's entries....

HTH,
Bernie
MS Excel MVP

 

[Tim Di Prinzio]

Tim,

Get a formula to work in the column where you want the new values to appear, something along the
lines of

=IF(AND(A2>3,A2<5),"New Value","")

and then when you have it working, record a macro where you select that cell, press F2 to enter Edit
mode, and then press Enter to enter the formula. Your code will look like this, for the formula
above entered into cell C2:

Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]>3,RC[-2]<5),""New Value"","""")"
Range("C3").Select
Range("C3").Select


Then change the code to include the entire C column:

Range("C2:C" & Cells(Rows.Count, 2).End(xlUp).Row).FormulaR1C1 = _
"=IF(AND(RC[-2]>3,RC[-2]<5),""New Value"","""")"

This code will match column B's entries....

HTH,
Bernie
MS Excel MVP

I was wondering if anyone could point me in the right direction on a few questions I have. I would
like to evaluate the value in a cell in a column and if it is within a certain range write a value
to a new column. I would like to do this for and entire column? Meaning write the function once and
have it work for the whole column.

Also is it possible to have a formula change the value of a cell and write the results back to the
same cell. i.e n1 has a value of 25. the value I really need is 25/2. This is on imported data. I
prefer not to write to another column. Basically as the data is imported or inputted the calc
takes place.

Thanks in advance for the help.

Regards,

Tim

Thanks Bernie -

But I think I need to do this with vba code as each cell could be a
different value. I think a case construct would work. But i am unsure
how to apply the vba code to work on the entire colunm...

Tim

 

[Bernie Deitrick]

Tim,

But I think I need to do this with vba code as each cell could be a different value. I think a
case construct would work. But i am unsure how to apply the vba code to work on the entire
colunm...

Each cell could be a different value - but if you can use one formula, you will still get different
results.

If each cell has unique upper and lower limits, try using cell references in your formula rather
than specific values:

=IF(AND(A2>B2,A2<C2),"New Value","")

etc.

HTH,
Bernie
MS Excel MVP

Tim,

Get a formula to work in the column where you want the new values to appear, something along the
lines of

=IF(AND(A2>3,A2<5),"New Value","")

and then when you have it working, record a macro where you select that cell, press F2 to enter
Edit mode, and then press Enter to enter the formula. Your code will look like this, for the
formula above entered into cell C2:

Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(AND(RC[-2]>3,RC[-2]<5),""New Value"","""")"
Range("C3").Select
Range("C3").Select


Then change the code to include the entire C column:

Range("C2:C" & Cells(Rows.Count, 2).End(xlUp).Row).FormulaR1C1 = _
"=IF(AND(RC[-2]>3,RC[-2]<5),""New Value"","""")"

This code will match column B's entries....

HTH,
Bernie
MS Excel MVP

I was wondering if anyone could point me in the right direction on a few questions I have. I
would like to evaluate the value in a cell in a column and if it is within a certain range write
a value to a new column. I would like to do this for and entire column? Meaning write the
function once and have it work for the whole column.

Also is it possible to have a formula change the value of a cell and write the results back to
the same cell. i.e n1 has a value of 25. the value I really need is 25/2. This is on imported
data. I prefer not to write to another column. Basically as the data is imported or inputted the
calc takes place.

Thanks in advance for the help.

Regards,

Tim

Thanks Bernie -

But I think I need to do this with vba code as each cell could be a different value. I think a
case construct would work. But i am unsure how to apply the vba code to work on the entire
colunm...

Tim